Migration

Scripted SQL Server Migrations

Use this batch file to automatically backup a database on one instance of SQL Server and restore it on a different instance of SQL Server.

Mike Wolfe

Jun 9, 2022 • 6 min read

I recently wrote about my steps for performing SQL Server migrations. Here's an excerpt of those steps:

Set old database to Read-Only
Back up the database on the old server
Restore on the new server
Remove read-only status on the new server

Those four steps are critical because they represent the downtime of your database during the migration. For that reason, I script those steps using the batch file shown below.

Using this process I can move a several-gigabyte live database from one server to another in under five minutes.

How the Script Works

Uses Ola Hallengren's Db Scripts

If you haven't already, you'll need to install Ola Hallengren's SQL Server scripts on both the old and new instances of SQL Server.

Configuration

The batch file uses environment variables to make configuration easy.

Simply set the seven variables in the "Configure Environment Variables" section at the top of the script. Be careful to use temp folder locations that your user account will have access to. I strongly suggest you perform a few practice runs before scheduling a final cutover.

Also, pay careful attention to which variables require UNCs/network folders and which are local to the respective SQL Server instance.

OldSqlServer: the server instance name (i.e., what you enter into the "Server name:" text box when connecting to the old server via SSMS) of the old server
OldTempFolder: a local folder on the old server that the old SQL Server service account has write access to; remember, this folder should be local from the perspective of the old SQL Server instance
OldTempFolderUNC: this is the path to the above folder from the perspective of the new server; the account executing the batch file needs at least read access to this location
NewSqlServer: the server instance name of the new server
NewTempFolder: a local folder on the new server that the new SQL Server service account has read access to
NewDataFolder: the parent folder where the SQL Server data files will be stored on the new server
NewCompatibilityLevel: the compatibility level you want your databases to use on the new server; you can leave this to match the old server for backward-compatibility purposes or set it to take advantage of new features if you are moving to a newer version of SQL Server

Steps For Each Database

The script allows you to migrate multiple databases in a single batch.

There's probably a way to loop through the database names in the batch file, but it wasn't worth it for me to figure out how to do that. I simply copy and paste the eight steps and change the value of the database name in step 1.

Here are the steps for each database:

Assign the database name to an environment variable
Backup database on old server
Move backup file to new server
Drop existing database on new server (to allow for practice runs)
Restore database backup file to new server
Remove the backup file from the new server
Set database "Read-only" property to False
Set compatibility level to take advantage of latest SQL Server features

Executing the Script

I strongly urge you to read my earlier article on SQL Server migration steps.

Even if you don't follow my exact steps, it will help you think through some of the issues that arise in a typical SQL Server migration. For example, before executing the script below, I manually set the old databases to Read-Only via SSMS.

Taking that approach gives me the chance to deal with any users who may still be working in the live production database, even if they were told not to. By keeping that step out of the script, it also allows you to safely perform a few practice runs.

That said, after each practice run you should be careful to either (A) drop the database(s) from the new server or (B) set them to read-only. This will prevent users from accidentally making changes to a database that is destined for deletion.

The Batch File

Save the contents below to a new file named MigrateDatabases.bat
Edit the seven configuration settings at the top
Change the database name in the first Step 1
Change the database name in the second Step 1 (or delete the second set of steps)
Make additional copies of steps 1 - 8 for each database you want to migrate

SOME FINAL WORDS OF WISDOM
Don't run scripts from the internet (including this one) unless you understand what they do.

REM ------------------------------------------------------------------------------
REM - Windows Batch file to automate the backup and restore of a database from
REM -    one SQL Server instance to another.
REM - Author: Mike Wolfe
REM - Source: https://nolongerset.com/scripted-sql-server-migrations/
REM - 
REM - USAGE INSTRUCTIONS:
REM -   Prepare your environment:
REM -       o Install db scripts from: https://ola.hallengren.com/ on both instances
REM -   
REM -   Configure the script for your environment:
REM -       o Set the six variables in the "Configure Environment Variables" section
REM -       o The old SQL Server instance must have write permission to %OldTempFolder%
REM -       o The new SQL Server instance must have read permission to %NewTempFolder%
REM - 
REM -   For each database to be migrated:
REM -       o Copy steps 1 through 8
REM -       o Change the DbName environment variable in Step 1
REM -       o Steps 2 through 8 should not be changed
REM - 
REM -   Before running the script [OPTIONAL]:
REM -       o Manually set the old database(s) to Read-Only (e.g., via SSMS)
REM -
REM -   Run the script from the new (i.e., destination) database server
REM -       o Use an account with permissions to the OldTempFolderUNC and NewTempFolder paths
REM ------------------------------------------------------------------------------

@ECHO off
CLS

REM ==============================================================================
REM --== Configure Environment Variables ==--
SET "OldSqlServer=MyOldServer"
SET "OldTempFolder=D:\SqlDumps\Migrate"
SET "OldTempFolderUNC=\\MyOldServer\d$\SqlDumps\Migrate"

SET "NewSqlServer=MyNewServer"
SET "NewTempFolder=G:\SqlDumps\Migrate"               
SET "NewDataFolder=C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA"
SET "NewCompatibilityLevel=150"
REM ==============================================================================

REM Breaking down the format 
FOR /f "tokens=2 delims==" %%G in ('wmic os get localdatetime /value') do set datetime=%%G

ECHO %datetime%

REM --== STEP 0. Building a timestamp from variables ==--
SET "dd=%datetime:~6,2%"
SET "mth=%datetime:~4,2%"
SET "yyyy=%datetime:~0,4%"
SET "Date=%yyyy%%mth%%dd%"
ECHO Built Date from variables: %Date%
ECHO/

SET "hh=%datetime:~8,2%"
SET "nn=%datetime:~10,2%"
SET "ss=%datetime:~12,2%"
SET "Time=%hh%%nn%%ss%"
ECHO Built Time from variables: %Time%



REM ==============================================================================
REM --== STEP 1. Set the database name here ==--
SET "DbName=MyDatabase"
REM ==============================================================================

REM --== STEP 2. Backup database on old database server ==--
osql -S %OldSqlServer% -d master -E -Q "EXECUTE dbo.DatabaseBackup @Databases = '%DbName%', @Directory = '%OldTempFolder%', @DirectoryStructure = Null, @BackupType = 'FULL', @FileName = '{DatabaseName}_%Date%_%Time%.{FileExtension}'"

REM --== STEP 3. Move to local server ==--
move %OldTempFolderUNC%\%DbName%_%Date%_%Time%.bak %NewTempFolder%

REM --== STEP 4. Drop existing database on local server ==--
osql -S %NewSqlServer% -d master -E -Q "EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'%DbName%'"
osql -S %NewSqlServer% -d master -E -Q "ALTER DATABASE [%DbName%] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE"
osql -S %NewSqlServer% -d master -E -Q "DROP DATABASE [%DbName%]"

REM --== STEP 5. Restore database backup to local server ==--
osql -S %NewSqlServer% -d master -E -Q "RESTORE DATABASE [%DbName%] FROM  DISK = N'%NewTempFolder%\%DbName%_%Date%_%Time%.bak' WITH  FILE = 1,  MOVE N'%DbName%' TO N'%NewDataFolder%\%DbName%.mdf',  MOVE N'%DbName%_log' TO N'%NewDataFolder%\%DbName%_log.ldf',  NOUNLOAD,  STATS = 5"

REM --== STEP 6. Remove backup file from file system ==--
del %NewTempFolder%\%DbName%_%Date%_%Time%.bak

REM --== STEP 7. Set database Read-Only to False ==--
osql -S %NewSqlServer% -d master -E -Q "ALTER DATABASE [%DbName%] SET  READ_WRITE WITH NO_WAIT"

REM --== STEP 8. Set Compatibility Level ==--
osql -S %NewSqlServer% -d master -E -Q "ALTER DATABASE [%DbName%] SET COMPATIBILITY_LEVEL = %NewCompatibilityLevel%"





REM ==============================================================================
REM --== STEP 1. Set the database name here ==--
SET "DbName=MyOtherDatabase"
REM ==============================================================================

REM --== STEP 2. Backup database on old database server ==--
osql -S %OldSqlServer% -d master -E -Q "EXECUTE dbo.DatabaseBackup @Databases = '%DbName%', @Directory = '%OldTempFolder%', @DirectoryStructure = Null, @BackupType = 'FULL', @FileName = '{DatabaseName}_%Date%_%Time%.{FileExtension}'"

REM --== STEP 3. Move to local server ==--
move %OldTempFolderUNC%\%DbName%_%Date%_%Time%.bak %NewTempFolder%

REM --== STEP 4. Drop existing database on local server ==--
osql -S %NewSqlServer% -d master -E -Q "EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'%DbName%'"
osql -S %NewSqlServer% -d master -E -Q "ALTER DATABASE [%DbName%] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE"
osql -S %NewSqlServer% -d master -E -Q "DROP DATABASE [%DbName%]"

REM --== STEP 5. Restore database backup to local server ==--
osql -S %NewSqlServer% -d master -E -Q "RESTORE DATABASE [%DbName%] FROM  DISK = N'%NewTempFolder%\%DbName%_%Date%_%Time%.bak' WITH  FILE = 1,  MOVE N'%DbName%' TO N'%NewDataFolder%\%DbName%.mdf',  MOVE N'%DbName%_log' TO N'%NewDataFolder%\%DbName%_log.ldf',  NOUNLOAD,  STATS = 5"

REM --== STEP 6. Remove backup file from file system ==--
del %NewTempFolder%\%DbName%_%Date%_%Time%.bak

REM --== STEP 7. Set database Read-Only to False ==--
osql -S %NewSqlServer% -d master -E -Q "ALTER DATABASE [%DbName%] SET  READ_WRITE WITH NO_WAIT"

REM --== STEP 8. Set Compatibility Level ==--
osql -S %NewSqlServer% -d master -E -Q "ALTER DATABASE [%DbName%] SET COMPATIBILITY_LEVEL = %NewCompatibilityLevel%"




PAUSE