Advertisements

Moving SQL Datafile to alternate location in SQL Server 2005

  use master
   go
   sp_detach_db ‘mydb’
   go
  • 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
  go
sp_attach_db ‘mydb’,’E:\folder2\mydbdata.mdf’,’E:\folder2\mydblog.ldf’
  go

 

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

use mydb
   go
   sp_helpfile
   go

The filename column values should reflect the new locations.

Advertisements

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:

WordPress.com Logo

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

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

%d bloggers like this: