Script to move SQL Server System Database Files

One issue that can occur when using VM snapshots to back up a server with large database volumes is that the server gets paused when the snapshots are consolidated. We like to refer to this a "stunning" because it sounds worse, and it can cause really bad things if you are using clusters and/or availability groups. If a cluster node gets stunned for more than a few seconds, the other node will think that it needs to fail over. Additionally, when the stun is over, the node that was stunned comes back to life right where it left off, and if it was the owner when it was stunned, all this has to be sorted out, and honestly, after working with Microsoft Cluster for twenty years, you just don't want to put it in this situation unless there's no way to avoid it.

So, your backup strategy changes, and you set your large volumes as independent disks so that they are ignored by the VM snapshots. This lets you backup the OS and any other small volumes without a stun. Your databases, of course, are being backed up by other means.

So a problem arises if your system databases are on one of those large volumes. Sure, you're backing them up, but have you thought of what happens if you have to restore the entire server from backup? You restore the volumes that were backed up by the VM snapshot, and then you just start restoring databases, right?

Well, hopefully, yes. But if the volume that held your master, model, and msdb databases wasn't part of the VM snapshot, SQL Server isn't going to start if your data volumes are gone. You'll have to go through the SQL Server install to get a new master database, and then you'll have to restore the backup of master and model from command line in order to get SQL Server started again. So this is going to add probably an hour to your recovery time.

After you've thought through it, you realize that moving master, model, and msdb files to the C: drive (or another volume that is getting the VM snapshot backups) is a good solution. If the are on C: and you have to restore the system from backup, SQL Server will be able to start up and you can start restoring databases as soon as the VM snapshot backup is restored.

No big deal--if you've only got three servers. Big deal, if you have a lot of servers. This would be quite time-consuming to go through the process of moving the files on every system. It's really nothing difficult--just running some queries, a couple of registry edits, moving the files, setting permissions.

So following is a PowerShell script, that when paired with some version-specific registry files to import, will move the system database and log files to a new location in just a few seconds. This is not set up to run remotely, so you'll need to run this on the server. I highly recommend that you review Move System Databases so that you understand what's happening. Each section of the script has comments so that you can easily see what's being done.

##############################################################################
###                                                                        ###
###   Set the new path for the system database files to be moved to        ###
###   This path needs to be paired with the path in the registry file(s)   ###
###                                                                        ###
            $MoveToPath = "C:\SystemDatabases\DATA"
###                                                                        ###
##############################################################################

function Pause()
{
  Write-Host "     Press any key to continue ..."
  $x = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")
  write-host
}

# Launch this script as administrator if not running as administrator
If (-NOT ([Security.Principal.WindowsPrincipal][Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator")){   
    $arguments = "& '" + $myinvocation.mycommand.definition + "'"
    Start-Process powershell -Verb runAs -ArgumentList $arguments
    Break
}

cls
write-host
write-host
write-host !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
write-host
write-host "     This script will stop SQL Server and move the system"
write-host "     databases to $MoveToPath."
write-host
write-host !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
write-host
Pause

#Set up the script enviroment
$myDir = Split-Path -Parent $MyInvocation.MyCommand.Path
Set-Location "$myDir"

$SqlServer = $ENV:COMPUTERNAME

#Create the new directory
New-Item -ItemType Directory -Path $MoveToPath

#Get the current locations of the system database files
$MasterMdfPath = (Invoke-SqlCmd -ServerInstance $SqlServer -Query "SELECT physical_name AS CurrentLocation FROM sys.master_files WHERE database_id = DB_ID(N'master') AND name = 'master';")[0]
$MasterLogPath = (Invoke-SqlCmd -ServerInstance $SqlServer -Query "SELECT physical_name AS CurrentLocation FROM sys.master_files WHERE database_id = DB_ID(N'master') AND name = 'mastlog';")[0]

$ModelMdfPath = (Invoke-SqlCmd -ServerInstance $SqlServer -Query "SELECT physical_name AS CurrentLocation FROM sys.master_files WHERE database_id = DB_ID(N'model') AND name = 'modeldev';")[0]
$ModelLogPath = (Invoke-SqlCmd -ServerInstance $SqlServer -Query "SELECT physical_name AS CurrentLocation FROM sys.master_files WHERE database_id = DB_ID(N'model') AND name = 'modellog';")[0]

$MsdbMdfPath = (Invoke-SqlCmd -ServerInstance $SqlServer -Query "SELECT physical_name AS CurrentLocation FROM sys.master_files WHERE database_id = DB_ID(N'msdb') AND name = 'MSDBData';")[0]
$MsdbLogPath = (Invoke-SqlCmd -ServerInstance $SqlServer -Query "SELECT physical_name AS CurrentLocation FROM sys.master_files WHERE database_id = DB_ID(N'msdb') AND name = 'MSDBLog';")[0]

#Change model database file paths
Invoke-SqlCmd -ServerInstance $SqlServer -Query "ALTER DATABASE model MODIFY FILE ( NAME = modeldev , FILENAME = '$MoveToPath\model.mdf' )"
Invoke-SqlCmd -ServerInstance $SqlServer -Query "ALTER DATABASE model MODIFY FILE ( NAME = modellog , FILENAME = '$MoveToPath\modellog.ldf' )"

#Change msdb database file paths
Invoke-SqlCmd -ServerInstance $SqlServer -Query "ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBData , FILENAME = '$MoveToPath\MSDBData.mdf' )"
Invoke-SqlCmd -ServerInstance $SqlServer -Query "ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBLog , FILENAME = '$MoveToPath\MSDBLog.ldf' )"

#Get the major SQL Server version so that the correct registry file can be found
$SqlVersion = (invoke-sqlcmd -ServerInstance $SqlServer -query "select SERVERPROPERTY('ProductMajorVersion')").Column1
write-host "SQL  Major Version: $SqlVersion"

#Set the registry file according to the version.  This sets the path to the master database and log files in the registry
#An improvement would be to make these registry changes with PowerShell so that additional files aren't needed
switch ($SqlVersion) {
   "10" {$RegFile = "2008_MoveMaster.reg"; break}
   "11" {$RegFile = "2012_MoveMaster.reg"; break}
   "12" {$RegFile = "2014_MoveMaster.reg"; break}
   "13" {$RegFile = "2016_MoveMaster.reg"; break}
   "14" {$RegFile = "2017_MoveMaster.reg"; break}
   default {"Unknown version of SQL Server!"; pause; exit}
}

#Import the registry file that changes the startup parameters for the master database files and data root
regedit.exe /S $RegFile

#Stop SQL Server services
stop-service sqlserveragent
stop-service mssqlserver

#Set permissions on the new directory so that the SQL Server service account has full control
$SqlServiceName = ((get-wmiobject win32_service | where {$_.Name -eq 'mssqlserver'}).StartName).replace(".\", "")
$Acl = get-acl $MoveToPath
$Ar = New-Object System.Security.AccessControl.FileSystemAccessRule("$SqlServiceName", "FullControl", "ContainerInherit,ObjectInherit", "None", "Allow")
$Acl.SetAccessRule($Ar)
Set-Acl $MoveToPath $Acl

#Move the system database files
Move-item -Path $MasterMdfPath -Destination "$MoveToPath\"
Move-item -Path $MasterLogPath -Destination "$MoveToPath\"

Move-item -Path $ModelMdfPath -Destination "$MoveToPath\"
Move-item -Path $ModelLogPath -Destination "$MoveToPath\"

Move-item -Path $MsdbMdfPath -Destination "$MoveToPath\"
Move-item -Path $MsdbLogPath -Destination "$MoveToPath\"

#Start the SQL Server services
start-service mssqlserver
start-service sqlserveragent

The registry files need to look something like this:

2017_MoveMaster.reg

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQLServer\Parameters]
"SQLArg0"="-dC:\\SystemDatabases\\DATA\\master.mdf"
"SQLArg2"="-lC:\\SystemDatabases\\DATA\\mastlog.ldf"

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\Setup]
"SQLDataRoot"="C:\\SystemDatabases"

2016_MoveMaster.reg

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQLServer\Parameters]
"SQLArg0"="-dC:\\SystemDatabases\\DATA\\master.mdf"
"SQLArg2"="-lC:\\SystemDatabases\\DATA\\mastlog.ldf"

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\Setup]
"SQLDataRoot"="C:\\SystemDatabases"

...and so on. You need to create a file for each major version you'll use it with and change the registry paths accordingly. If you don't understand this, you probably ought not to use this script to move your system databases.