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'