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

[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

"Peter Bromberg [C# MVP]" <pbro...@yahoo.NoSpamMaam.com> wrote in message
news:8D2C8562-289F-402C...@microsoft.com...

[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?

[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" <jw...@softcircuits.com> wrote in message
news:erCFkMYn...@TK2MSFTNGP04.phx.gbl...

[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

"Norman Yuan" <Fake...@FakeEmail.Not> wrote in message
news:%23edvFOb...@TK2MSFTNGP06.phx.gbl...