SQL Database Has Overstayed its Welcome

[Jonathan Wood]

I have a Web application. Initially, I created a database in the App_Data
folder and then attached it to SQL Servero 2005 (not SQL Server Express).

I realize now that this is now what I want. So I created a backup of the
database, detached the database file, and then created a new database from
the backup that is fully a part of SQL Server 2005.

The new database works just fine but I'm having the following problems:

1. I can delete my old connections in the Visual Studio Server Explorer
window just fine. However, they always come back whenever I restart VS. I
really don't want a bunch of garbage laying around that serves no purpose.

2. I'm not able to delete the files from my App_Data folder. Apparently,
there is still some sort of lock on two of the files (*.mdf and *.ldf).
Again, I would really like to clear out this old junk.

Can anyone offer any tips?

Thanks.

 

[Peter Bromberg [C# MVP]]

Try stopping the SQL Server and / or SQLEXPRESS services. This should release
the pesky files. Then restart the service(s).
-- Peter
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
Short Urls & more: http://ittyurl.net

 

[Norman Yuan]

The OP mentioned he has already detached to old database. If so, he should
be able to delete the *.mdf/*.ldf used by that old database without need to
stopping SQL Server services.

To the OP:

Since you created the database again using the backup, are you sure you
changed the database to other location than App_Data folder? You can easily
verify where the new database' physical files located using SSMS before
trying to stop SQL Server service and delete *.mdf/*.ldf file

 

[Jonathan Wood]

Norman,

To the OP:

Since you created the database again using the backup, are you sure you
changed the database to other location than App_Data folder? You can
easily verify where the new database' physical files located using SSMS
before trying to stop SQL Server service and delete *.mdf/*.ldf file

I think this is my problem. I just assumed a backup was just the database
data and didn't examine this type of information.

My understanding is that, if I simply create a SQL Server database (not a
database file), there there is some system location where it is actually
locatied. Can you tell me how I can essentially duplicate that arrangement?

Thanks.

 

[Norman Yuan]

SQL Server has its default data file location, by default, it is "C:\Program
Files\Microsoft SQL Server\[MSSQL.1]2]...\MSSQL\Data". So, if you create new
database, the db's *.mdf/*.ldf file will be created there. You can change
the default data file location by using SSMS easily. However, if you attach
*.mdf file to SQL Server, the data file stays where it was after attaching.


I am not sure what you did, but if the *.mdf file cannot be deleted/copied,
then the data file must belong to an existing database and still in use. As
I said, you can use SSMS to find out a database' data is stored in which
location. I'd never simply stop SQL Server service and delete *.mdf file
without doing this check first.

 

[Jonathan Wood]

Yes, what you describe is correct. I can verify this is the case. But I'm
still at a loss as to how to change it.

I backed up the databases I want to change. But if I select New Database in
SSMS, I don't see an option to read from a backup. And if I select Restore,
I went in to options and changed the database name but it still restores to
the original location.

Can't someone tell me how to make this database behave as though I just
created it through SQL Server, using the default database file location?

Thanks.

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

SQL Server has its default data file location, by default, it is
"C:\Program Files\Microsoft SQL Server\[MSSQL.1]2]...\MSSQL\Data". So, if
you create new database, the db's *.mdf/*.ldf file will be created there.
You can change the default data file location by using SSMS easily.
However, if you attach *.mdf file to SQL Server, the data file stays where
it was after attaching.


I am not sure what you did, but if the *.mdf file cannot be
deleted/copied, then the data file must belong to an existing database and
still in use. As I said, you can use SSMS to find out a database' data is
stored in which location. I'd never simply stop SQL Server service and
delete *.mdf file without doing this check first.

Norman,


I think this is my problem. I just assumed a backup was just the database
data and didn't examine this type of information.

My understanding is that, if I simply create a SQL Server database (not a
database file), there there is some system location where it is actually
locatied. Can you tell me how I can essentially duplicate that
arrangement?

Thanks.