Automate Database Restore for SQL Server

By:   |   Updated: 2020-07-06   |   Comments (4)   |   Related: More > Restore


I frequently get requests to refresh a test / dev SQL Server database and need a way to test database backups from the latest full backup of a production database. It’s easy enough to do this with SQL Server Management Studio, but I would rather have an easy to configure script that’s less prone to making a mistake that I could run on demand and / or easily automate rather than do this as a manual process.


We previously saw how to accomplish this in this tip: Automating a SQL Server Database Refresh, with a little PowerShell, a little T-SQL and SQL Server Agent. In the interest of keeping the configuration simpler and only changed in one script the following is a revised way to do it with just PowerShell and optionally SQL Server Agent.

The following versions were used writing this tip:

  • SQL Server 2017 CU19 Developer Edition
  • SQL Server Management Objects (SMO) installed SQL Server Management Studio 18.4
  • PowerShell 5.1.17763.1007

Configure PowerShell Script

To get started, copy the following PowerShell into the editor of your choice and configuring the variables for your purposes.

# restores a full database backup to another database from source's latest full backup file in specified directory
# begin script configuration here
$TargetSqlServerInstance = "JGAVIN-L\SQL2017"                                                                        # target server instance 
$TargetDb = "RefreshTest"                                                                                            # target database 
$BackupDir = "\\jgavin-l\SQL2017_Backup\RefreshProd"                                                                 # directory / share where backups are stored
$SourceLogicalDataFileName = "RefreshProd"                                                                           # logical data file name of source db 
$SourceLogicalLogFileName =  "RefreshProd_log"                                                                       # logical log file name of source db
$TargetLogicalDataFileName = "RefreshTest"                                                                           # logical name you want to change logical data file on target db to
$TargetLogicalLogFileName =  "RefreshTest_log"                                                                       # logical name you want to change logical log file on target db to
$TargetPhysicalDataFileName = "C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\RefreshTest.mdf"     # full path\file of target db physical data file 
$TargetPhysicalLogFileName =  "C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\RefreshTest_log.mdf" # full path\file of target db physical log file 
$CompatLevel = 140                                                                                                   # compatibility level to set target database to (2019=150, 2017=140, 2016=130, 2014=120, 2012=110, 2008/2008R2=100, 2005=90, 2000=80, 7=70) 
# end script configuration here
# import sqlserver module
Import-Module sqlserver
# latest full backup file name is dynamically determined and appended to backup directory
$LatestFullBackupFile = Get-ChildItem -Path $BackupDir -Filter *.bak | Sort-Object LastAccessTime -Descending | Select-Object -First 1 
$FileToRestore = $BackupDir + '\' + $LatestFullBackupFile
# kill any connections in target database
USE master
USE master
USE master
Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query $KillConnectionsSql 
# import sqlserver module
Import-Module sqlserver
# restore
$RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("$SourceLogicalDataFileName", "$TargetPhysicalDataFileName")
$RelocateLog  = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("$SourceLogicalLogFileName",  "$TargetPhysicalLogFileName")
Restore-SqlDatabase -ServerInstance $TargetSqlServerInstance -Database $TargetDb -BackupFile $FileToRestore -RelocateFile @($RelocateData,$RelocateLog) -ReplaceDatabase
# end restore
# set db owner to sa
Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Database $TargetDb -Query "EXEC sp_changedbowner sa"
# set compatibility level
Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query "ALTER DATABASE$($TargetDb) SET COMPATIBILITY_LEVEL =$($CompatLevel)"  
# set recovery model to simple
Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query "ALTER DATABASE$($TargetDb) SET RECOVERY SIMPLE WITH NO_WAIT"  
# rename logical files
Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query "ALTER DATABASE$TargetDb MODIFY FILE (NAME='$SourceLogicalDataFileName', NEWNAME='$TargetLogicalDataFileName')"
Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query "ALTER DATABASE$TargetDb MODIFY FILE (NAME='$SourceLogicalLogFileName', NEWNAME='$TargetLogicalLogFileName')"
# dbcccheckdb
Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query "DBCC checkdb ($TargetDb) --WITH NO_INFOMSGS"
# display sp_helpdb
Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query "EXEC sp_helpdb$($TargetDb)" 

The following table lists each configurable variable and its description:

Variable Description
$TargetSqlServerInstance Name of SQL Server with database we’re restoring to in the form: MachineName or MachineName\InstanceName
$TargetDb Name of target database we’re restoring to
$BackupDir Path to local drive or UNC share with backup files in the form: \\ServerName\ShareName or DriveLetter:\DirectoryName
$SourceLogicalDataFileName Logical data file name of source database
$SourceLogicalLogFileName Logical log file name of source database
$TargetLogicalDataFileName Logical data file name of target database
$TargetLogicalLogFileName Logical data file name of target database
$TargetPhysicalDataFileName Fully qualified path to physical data file of target database
$SourceLogicalLogFileName Fully qualified path to physical log file of target database
$CompatLevel Compatibility Level we want for target database

To obtain the logical and physical file names for the source and target databases:

From Object Explorer in SQL Server Management Studio:

  1. Right click on source database
  2. Properties
Obtain source database logical file names
  1. Files
Source database logical file names

Repeat for the target database.

  1. Right click on source database
  2. Properties
Obtain target database logical and physical file names
  1. Files
Target database logical and physical file names

Our source database full and transaction log backups are saved in the C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Backup\RefreshProd directory. Looking at the time and date stamps, we see that RefreshProd_full_backup_2.bak is the latest full backup, and that’s the one we want to refresh the test database with. The fully qualified name is determined by the string in $BackupDir appended with the dynamically generated string in $LatestFullBackupFile.

Source database backup files

Execute Script

After configuring the variables, I’ve saved the PowerShell as C:\DbaScripts\RefreshTestDb.ps1. Execute it and we see the output of sp_helpdb on our newly refreshed database.

RestoreTestDb execution

The SQL Server errorlog shows us:

  1. C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Backup\RefreshProd, RefreshProd_full_backup_2.bak was restored
  2. Restore completed
  3. Compatibility Level was set
  4. Database recovery model set to SIMPLE
  5. DBCC CHECKDB ran without errors

Automate Script in a SQL Server Agent Job

Now, we’ll automate it in a SQL Server Agent Job.

  1. Expand SQL Server Agent dropdown
  2. Right click Jobs
  3. New Job…
New SQL Agent Job
  1. Name Job
  2. Set Job owner
  3. Enter Job description
  4. Steps
New Job
  1. New
New Step
  1. Name Step
  2. Choose Operating System (CmdExec) in Type dropdown
  3. Enter PowerShell executable name with a -File=FullyQualifiedScriptName
  4. OK
New Job Step
  1. Schedules
  2. New
New Job Schedule
  1. Name Job Schedule
  2. Choose Frequency
  3. OK
Configure New Job Schedule
  1. OK
Create Job
  1. Expand Jobs
  2. Right click on new Job
  3. Start Job at Step…
Execute Job

Look for Success in Status column.

Job Status
  1. Right click on Job
  2. View History
View Job History
Job History
Job Step History
Next Steps

Here are links to tips with a wealth of information on the following related topics:

Last Updated: 2020-07-06

get scripts

next tip button

About the author
MSSQLTips author Joe Gavin Joe Gavin is from Greater Boston. He has held many roles in IT and is currently a SQL Server Database Administrator.

View all my tips
Related Resources

Comments For This Article

Monday, August 10, 2020 - 9:17:50 AM - Joe Gavin Back To Top (86265)
Thanks Simon. Definitely considered dbatools. They're fantastic. We decided a requirement for this tip was to be be purely native with nothing additional installed.

Sunday, August 09, 2020 - 9:24:14 AM - Simon Back To Top (86260)
Hi, nice article. Have you considered using the Powershell suite ?

Monday, July 06, 2020 - 9:58:35 AM - Joe Gavin Back To Top (86091)

Thanks Robert.

Monday, July 06, 2020 - 8:39:30 AM - robert rogers Back To Top (86090)

I'm not a big fan of PowerShell when it comes to SQL Server...but this great post. Thanks for write up.


Recommended Reading

Identify when a SQL Server database was restored, the source and backup date

How to migrate a SQL Server database to a lower version


Make Network Path Visible For SQL Server Backup and Restore in SSMS

SQL Server Database Stuck in Restoring State

get free sql tips
agree to terms

Learn more about SQL Server tools