Detaching a database


September 26, 2013 by Kenneth Fisher

In my general quest to figure out the T-SQL to do common GUI tasks I did some browsing through BOL to find out how to detach a database. Dropping a database is easy enough.

DROP DATABASE <databasename>

But that also deletes the database files (unless the database or one of its files is offline). Detaching a database on the other hand leaves the database files intact. Why should you care? Well in my particular case I was asked to drop a set of databases that had recently been moved into a new production environment. It’s been a few weeks since the move and the chances of someone needing these databases is really pretty small. However I’m the cautious sort. I want to be able to get the databases back in case someone starts screaming later today. Yes these databases are regularly backed up, and I could always restore them at need. On the other hand by detaching them and not deleting the database files all I have to do is reattach and I’m done.

So the first thing I did was back up the locations of the files. On a big server, with lots of DBAs over time, creating lots of databases, I can’t be certain where the files were put when the database was created. By backing up the file locations to a table in my “DBA” database I can find them fairly easily at need. To do this I ran the following query.

SELECT * INTO DBA.dbo.<dbname>_Files 
FROM sys.master_files 
WHERE name = 'dbname';

At this point I can detach my database using the GUI

Detaching a database

Or like I said I can find and use the T-SQL method.

EXEC sp_detach_db <dbname>;

Now you could say I probably spent more time looking up the detach stored procedure than I would have spent just using the GUI, but first of all I like knowing both methods, and second of all I was detaching 5 databases. In my particular case the T-SQL was in fact the faster method.

One last point. Once I had the list of files in place and had detached the databases from the instance I made a point of setting myself a reminder to go back and delete the files at an appropriate time. Otherwise I’m libel to forget and the files are just going to sit there until someone audits the database files in use vs the database files on the drive. And who knows when that’s going to happen. Certainly not any time soon.

One thought on “Detaching a database

  1. Olger Eras Ríos says:

    I’m totally agree, it’s better to keep the database files for a while as long as there is free space to save them and until we could be sure we don’t need them anymore.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,755 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: