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.

--
Jonathan Wood
SoftCircuits Programming

http://www.softcircuits.com

[=?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=]

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

>
>

[Norman Yuan]

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]" <pbromberg@yaho o.NoSpamMaam.co mwrote in message
news:8D2C8562-289F-402C-882E-AA184EAB5425@mi crosoft.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
>>
>>

[Jonathan Wood]

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

[Norman Yuan]

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" <jwood@softcirc uits.comwrote in message
news:erCFkMYnIH A.4196@TK2MSFTN GP04.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

>

[Jonathan Wood]

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" <FakeName@FakeE mail.Notwrote in message
news:%23edvFObn IHA.980@TK2MSFT NGP06.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" <jwood@softcirc uits.comwrote in message
news:erCFkMYnIH A.4196@TK2MSFTN GP04.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
>>

>