SQL Database Has Overstayed its Welcome

  • Thread starter Thread starter Jonathan Wood
  • Start date Start date
J

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.
 
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
 
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
Click to expand...

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.
 
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.
 
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

Norman Yuan said:
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 said:
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.
Click to expand...
Click to expand...
 
Back
Top