During my last engagement with a client, I was asked to provide steps to move SQL databases from H drive to J drive. H drive contains all user and system databases. This was a clustered instance, so we need to take care of dependency also. Here are the steps which I have outlined for Migrate Clustered Disk.
- ALTER ALL databases except master to change the drive letter to J: drive. We used the following commands to change system database metadata before taking the SQL resource offline:
USE master; GO ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev ,FILENAME = N'J:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb.mdf' ) GO ALTER DATABASE tempdb MODIFY FILE ( NAME = templog ,FILENAME = N'J:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\templog.ldf' ) GO
ALTER DATABASE model MODIFY FILE ( NAME = modeldev ,FILENAME = N'J:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\model.mdf' ) GO ALTER DATABASE model MODIFY FILE ( NAME = modellog ,FILENAME = N'J:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\modellog.ldf' ) GO
ALTER DATABASE msdb MODIFY FILE ( NAME = msdbdata ,FILENAME = N'J:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\msdbdata.mdf' ) GO ALTER DATABASE msdb MODIFY FILE ( NAME = msdblog ,FILENAME = N'J:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\msdblog.ldf' )
- Same thing is needed for ALL user databases. We have not put the command because they would vary based on your server setting.
- Update at this registry key on both the nodes: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server find all references to H: and change it to J:
- Startup parameters to be updated from SQL Server Configuration manager to reflect the new path. This is where we need to change path for MASTER database files.
- Shut SQL Server down by taking it offline, but leave the clustered disk resources online.
- Copy everything exactly from Old disk(s) H drive to New Disk(s) J drive.
- Change the SQL Server group dependencies, so that the SQL Server service is dependent on each disk resource.
- Take the old disks out of the resource group.
- Bring SQL resource online.
Test failover and failback!
If you think something is missing, please comment!
Reference: Pinal Dave (https://blog.sqlauthority.com), Image Courtsey: Microsoft