Moving SQL Datafile to alternate location in SQL Server 2005

  use master
   sp_detach_db ‘mydb’
  • Copy the data files and the log files from the current location (D:\folder1\Data) to the new location (E:\folder2).
  • Reattach the database. Point to the files in the new location as follows:
use master
sp_attach_db ‘mydb’,’E:\folder2\mydbdata.mdf’,’E:\folder2\mydblog.ldf’


To verify that the change has taken place; run the below command

use mydb

The filename column values should reflect the new locations.


About Sunil Padmanabhan

An Indian based out of Dubai who started traveling from 2011 and pretty much caught the travel bug. Every year presents itself as an opportunity to visit and explore a new place.

Posted on September 16, 2013, in SQL Server, SQL Server 2005 and tagged , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: