Monday, April 18, 2011

How to move SQL Server Databases to new location

When you plan to move user databases files to new location please follow the steps.

1. Make sure users are disconnected and planned down time

2. Make sure to take a Full backup of the databases

3. Find the data files and log files names and current location

use  master
go
SELECT name, physical_name FROM sys.master_files
WHERE database_id = DB_ID('My_dbname')

4. Set the database you want to work with offline
ALTER DATABASE My_dbname SET OFFLINE
go

 
5. Move one file at a time to the new location
ALTER DATABASE My_dbname
MODIFY FILE ( NAME = My_Data, FILENAME = 'E:\MSSQL\Data\My_Data.mdf')
go

 
6. Repeat the Step 5 until you move the all the data and log files to new location


7. Set the database online
ALTER DATABASE My_dbname SET ONLINE
go

No comments: