How to use Detach and Attach functions to move SQL Server databases

So you are running low on disk space on your database drive and you really don’t have time to shut-down your production environment so you can extend your drive.. but you have some space available on your log drive and backup drive.. Well if you fit this, you were me yesterday, congratulations! So here is a quick run-through on how not to get get run through. 1. What do you have?

use <database_name>
exec sp_helpfile

this will give you the current DB information.2. Detach.

use master
exec sp_detach_db 'db'

This does what it says and will have you disconnected in no time at all, if this is on production.. and I really hope it is not, first prepare the next batch of code and your file copy/move to the new directory before detaching.

3. Copy the mdl/ldf files to the new location.

4.Reattach

use master
exec sp_attach_db 'mydb','E:\DataBase\dbdata.mdf','E:\DataBase\dblog.ldf'

5. Check

By running the code from Step 1 you can verify your files are in the new location.

This is more or less the same as from Microsoft http://support.microsoft.com/kb/224071

Before doing anything like this just for kicks be sure you know what you are doing. If you have a DB attached over a network share there are other considerations then working on a local machine. Depending on your SAN configuration this might affect performance if this is a highly utilized DB instance. Are you writing your DB to the same physical drive as your log files? How will it affect your overall performance?

If you are not deeply familiar with your SQL Server farm I really do not suggest just doing anything like this, however it works great when someone comes walking over to you with a black tag shouting that there is 0KB available on the DB drive.

Also, if you have your autogrowth set properly you might have a couple of minutes or even hours warning about a impending disk space issues as the disk space will be used up by the autogrowth event and leave unused space to be filled by SQL server.

Adrian