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

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:

WordPress.com Logo

You are commenting using your WordPress.com 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: