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

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.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, SSMS, System Functions and Stored Procedures, T-SQL Tagged: code language, microsoft sql server, sql statements, SSMS, system functions, T-SQL