Printed 2017/07/28 12:49AM

SQL Server : Moving System, resource and User Databases to New SAN disk


In my earlier post I have explained the steps that we have followed to move the MSDTC and Quorum drive to the new SAN. In this post let us go through the steps that we have followed to move the System,Resource and User databases to the new SAN.The environment that we are talking about is running on windows 2008 server and SQL server 2008

 When this task assigned to me, I did not had any idea about the steps that need to be followed. The first thing came in mind was copying the entire contents of each drive to the respective new drive and change the drive letter, but that is a time consuming process and need lot of down time. After spending lots of time, we came up with a plan with minimal downtime. Let us go through the steps.We stared our process from Wednesday and finished on Sunday. I will explain the steps that we have followed on each day.



SELECT 'Backup database ['+name +'] to disk =''R:\DISKmoveFullBackup\'+ REPLACE(name,' ','')+'_Diskmovefriday.bak'' with password =''Password@121'''   FROM sys.databases WHERE name NOT IN ('MAster','model','msdb','tempdb','distribution')
SELECT database_id,DB_NAME(database_id),FILE_ID,type_desc,name,physical_name,state_desc FROM sys.master_files


The first task on Friday is to restore all user database as _New in the new disk. In our environment we have equal number of new disks. As a first step we need create the same folder structure new disk  as in the old disk. As it is data drives , the folder structure may not be complicated  to create. You can ignore the folder structure where the system files resides. At later point we will be copying the entire folder to the new drive.This script can be used to generate the restoration script based on the latest full backup.

The next step is to change the default data/log path and backup path. To do that on the server node in the object explorer and select properties.On left pane of the property window select the database settings and change the default location of data and log. Using the regedit change the value of BackupDirectory key under the HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL   Server\MSSQL10.instname\MSSQLSERVER

Disable any full backup job that is schedule to run before the disk movement maintenance windows. Any full backup will break the backup chain.If the environment have the mirroring/replication , prepare complete script to remove and create replication and mirroring.

Sunday : The Big Day

Take differential backup of all user database and apply on top of the _new database which we created on Friday.The output of the below script can be used to take the differential backup.Before starting the differential backup, disable all the transaction log backup jobs and make sure that none of the transaction backup jobs are  running at the point.

SELECT 'backup database ['+name +'] to disk =''R:\DiskMoveDiffBackup\'+REPLACE(name,' ','')+'_diffsunday.bak'' with DIFFERENTIAL'FROM sys.databases WHERE name NOT IN ('Tempdb','Master','model','msdb' , 'distribution') AND name NOT LIKE '%_new'

To restore the differential backup, this script can be used. Once the restoration of differential backup  completed, the actual maintenance window starts. It will take  minimum of 30 minutes ,depends on the number of database and time required to recover the database after the transaction log restoration. In our environment (with around 60 database and approx 1 TB size)  it took around an hour to complete the process. Recovering the database after the transaction log backup is the most time consuming process.

Follow the below steps once the maintenance window start:
  1. Disable all logins used by the application. This will be helpful to avoid unnecessary connection request from the application.
  2. Stop the SQL server agent service from the cluster admin window.
  3. Kill all existing user session especially from application and agent service.
  4. Remove mirroring and replication using the script which we have prepared on Friday.
  5. Take transition log backup of all user database and rename the existing database as _OLD.
  6. Restore the transaction log backup on top of _new databases with recovery.
  7. Rename _new database (remove the _new).Each section of this script will help us to perform the the steps 5,6 and 7.
  8. Detach and attach the distribution database to new drive.
  9. System databases and resource database will be there in one of the disk and moving them to new disk is a tedious task. We followed  following step to do that
    1. Identify the drive in which the system database and resource database resides .In our case it was M drive and associated new drive is U.
    2. Alter all database file which is  there in M drive (many _old database) to point to U drive.This script will be useful to perform this task.
    3. Alter all database file which is there in U drive (newly restored databases) to point to M drive.This script  will be useful to perform this task.
  10. Alter the tempdb database to point the data and log file to new disk.
  11. Bring down the instance offline through the cluster admin tool. Make sure that that, disks are online.
  12. Copy the system database root folder from M drive to U drive with all sub folders. We have used the XCOPYcommand to move the all the files along with folder structure to the new drive.
  13. Swap the drive letter M and U. We can perform this task through the cluster admin.
  14. Bring the instance online.
  15. Set up the replication
  16. Enable the logins disabled in the step 1 and the instance is ready to use.
  17. Set up the mirroring.
  18. Change recovery model to simple for those recovery changed as part of Thursday task.
  19. Enable all backup jobs.
  20. Drop the _OLD database and remove the dependency on old disks.
  21. Now the old disks will be available under the 'Available disk group'. Right click on each one and delete.
  22. Inform your SAN team 
Thank you for reading such long post !

If you liked this post, do like my page on FaceBook at

Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.