Friday, November 02, 2012

Moving Sql data files to different location


It is always good to have database files to be placed on a non-system partition (system partition is where default program files and windows folder resides)

Last week, unfortunately my PC got corrupted, had to restore to 3 days old system image of windows, and I lost my sql data. Now for future, I have taken a preventive measure, and moved my sql database files to non-system drive.

Here are the steps,

Database name is testDB,

Note: make sure new location is not read only, has sufficient privileges to Administrator account, and the drive is uncompressed 

Step 1 : Create a new location 

New location is D:\MSSQL\DATA\

Step 2 : Run these commands in master database

ALTER DATABASE testDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE testDB SET OFFLINE;

ALTER DATABASE testDB MODIFY FILE
(
   Name = testDB,
   Filename = 'D:\MSSQL\DATA\testDB.MDF'
);

ALTER DATABASE my MODIFY FILE
(
   Name = testDB_log,
   Filename ='D:\MSSQL\DATA\testDB_log.LDF'
);

Step 3 : Move original sql data files from C:\program files\mssql\data to newly created location

Make sure the newly pasted files are not read-only, remove the read-only or compression settings.


Step 4: Move original sql data files from C:\program files\mssql\data to newly created location

Bring the database to online mode

ALTER DATABASE testDB SET ONLINE;
ALTER DATABASE testDB SET MULTI_USER;