Moving SQL Datafile to alternate location in SQL Server 2005
- Start SQL Server 2005 Management Studio. To do this, click Start, click All Programs, click Microsoft SQL Server 2005, and then click SQL Server Management Studio.
- Click New Query, and then detach the database as follows:
- 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:
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 goThe filename column values should reflect the new locations.
Advertisements
Posted on September 16, 2013, in SQL Server, SQL Server 2005 and tagged Database, move data file. Bookmark the permalink. Leave a comment.
Leave a comment
Comments 0