SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Detaching a database

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.

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


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.


Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...