SQL Database Has Overstayed its Welcome

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.

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

RE: SQL Database Has Overstayed its Welcome

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


"Jonathan Wood" wrote:

> 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.
>
> --
> Jonathan Wood
> SoftCircuits Programming
> http://www.softcircuits.com
>
>

Re: SQL Database Has Overstayed its Welcome

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

"Peter Bromberg [C# MVP]" wrote in message
news:8D2C8562-289F-402C-882E-AA184EAB5425@microsoft.com...
> 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
>
>
> "Jonathan Wood" wrote:
>
>> 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.
>>
>> --
>> Jonathan Wood
>> SoftCircuits Programming
>> http://www.softcircuits.com
>>
>>

Re: SQL Database Has Overstayed its Welcome

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.

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

Re: SQL Database Has Overstayed its Welcome

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" wrote in message
news:erCFkMYnIHA.4196@TK2MSFTNGP04.phx.gbl...
> 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.
>
> --
> Jonathan Wood
> SoftCircuits Programming
> http://www.softcircuits.com
>

Re: SQL Database Has Overstayed its Welcome

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" wrote in message
news:%23edvFObnIHA.980@TK2MSFTNGP06.phx.gbl...
> 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" wrote in message
> news:erCFkMYnIHA.4196@TK2MSFTNGP04.phx.gbl...
>> 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.
>>
>> --
>> Jonathan Wood
>> SoftCircuits Programming
>> http://www.softcircuits.com
>>
>