Moving SQL Server database to another drive/folder



You may find many pages describing how to move sql server database to another location. But a beginner may wonder what needs to be done with the following commands:

use master
go
sp_detach_db ‘mydb’
go

Basically, the above commands are just a set of queries to be run from within a tool like “Microsoft SQL Server Management Studio Express”.

KNOW WHERE THE DATA FILES ARE

In order to move the database, first you will need to know where they are currently stored. This you can find by right clicking on the database in Object Explorer and choosing Properties. In the Properties window you will find the details under the ‘Files’ pane.

Notice the path of your database and open that folder in Windows Explorer so that you can move the data files after detaching the database in the below steps.

DETACHING THE DATABASE

1. Run Microsoft SQL Server Management Studio Express
2. Connect to the required SQL Server
3. Go to File->New and choose “Database Engine Query”
4. Paste the following commands (of course you need to replace the mydb with your database name)

use master
go
sp_detach_db ‘mydb’
go

5. Choose ‘Execute’ from the toolbar. That’s it. This will detach the data files from the database.

MOVING THE DATA FILES

Now, move the data files (*.mdf and *.ldf) to a new drive/folder.

ATTACHING THE DATABASE

To attach the files with the database, replace the previous commands with the following commands and execute.

use master
go
sp_attach_db ‘mydb’,’E:\newlocation\mydbdata.mdf’,’E:\newlocation\mydblog.ldf’
go

VERIFYING THE DATABASE

To check whether attaching was successful, replace the previous commands with the following commands and execute.

use mydb
go
sp_helpfile
go

That’s it. Thanks. Hope this post is clear. If not please do let me know.