4

I'm working with MS SQL Express 2008 and Visual Studio 2010.

If you are attached to a local MDF file how do you remove it from the Databases list without deleting the file? The reason is to purge the list of databases for old projects. The next time you run the project it will re-establish the connection so it makes sense to make things tidy and easy to find when you need it.

Trying to simply delete the the database of course deletes the files as well. I tried unchecking the Delete backup and restore history information for databases but that didn't make a difference. I also tried the ole' Shift-Delete to alter its functionality.

Odd thing is when I search I don't see simlar questions. I'd think that would be something others would want to do.

Right now I make a copy of the MDF and LDF files, delete the database from Management Studio and then copy the file back.

Is there a better way?

7

You could detach the database, that way the file isn't deleted and it won't display inside of SSMS.

  • That pretty much what I've tried to do, but I don't see a detach option. – Rich Shealer Jul 8 '12 at 17:31
  • sp_detach_db '[dbname]'; – swasheck Jul 8 '12 at 20:56
  • 1
    If you right click the database you want to remove, one of the choices is Tasks.... The very first sub-menu choice is Detach.... This will open a window allowing you to detach the selected database. Simple when you know where to look. I tried the stored procedure but it was complaining the database was in use. I had everything else closed, so I'm not sure why. It also didn't help that I was search for dettached with two Ts. – Rich Shealer Jul 9 '12 at 0:18
3
  1. Right click the database in SSMS
  2. Click Tasks
  3. Click Detach

enter image description here

To attach the database:

  1. Right click the databases node
  2. Select Attach
  3. Add the MDF file. The LDF file will be added automatically if it's in the same folder

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.