How to Automate SQL Server Restores for a Test Server

By:   |   Comments (16)   |   Related: > Restore


I know that if you don't test a database backup, you don't know if it's good or not. I'd like to automate not only the backup of the database, but also restoring of the backups to a test server. There's no money in the budget for tools to assist me. How can I do this?  Check out this tip to learn more.


Likely if you're working with a limited budget you're already scheduling your backups using SQL Server Agent. Not only can we automate the backup of the database, but we can also automate the restore to a second server.

SQL Server Agent Job for Backup, Restore and Database Drop

The typical way is to add job steps after the backup to do the restore on the test server. For instance:

SQL Server Agent Job Steps to Backup and Restore a Database

Since this is an example, here's the very simply T-SQL to generate the backup in step 1:

TO DISK = N'c:\temp\backups\pubs_full.bak' 

With steps 2 and 3, we're going to make a connection using a CmdExec script and we'll execute the query using SQLCMD. Here's step 2:

sqlcmd -E -S 2008R2 -b -i "c:\temp\backups\pubs_RestoreTest.sql"

Note that I'm calling a SQL script file because when you do a restore to a test server, you may need to move files around from their default location. That's the case here. Here are the contents of that script file.

FROM DISK = N'c:\temp\backups\pubs_full.bak'
WITH MOVE 'pubs' TO 'c:\temp\backups\pubs_RestoreTest.mdf',
MOVE 'pubs_log' TO 'C:\temp\backups\pubs_RestoreTest_log.ldf',

Step 3 is similar, except we're going to simply drop the database, so we'll use a command-line query and exit:

sqlcmd -E -S 2008R2 -b -Q "DROP DATABASE pubs_RestoreTest;"

This simple approach works most of the time. If you were to test it and both servers are up, security is correct, etc., you'll get a result like the following:

Successful Backup Message from SQL Server Agent

But what if there is an issue? Then you'll see this:

Error from SQL Server Agent Backup and Restore

The problem is that with everything lumped together in one SQL Agent job, you don't know if the failure was in taking the backup or attempting the restore. As we see here, the problem is in the restore:

SQL Agent Job Step Status

If you have an OLA/SLA, it's likely only on the database backups. If there's an issue with the test server, you want to know that separately. If that's your case, you'll probably want to do something a bit more complex, but more effective.

Separating the SQL Server Backup from the Restore

The solution is to have two different jobs: one for the backup and another for the test restore. You can call the second job from the first. Once you get it right (it isn't hard), the call to start the second job should always succeed unless there's an issue with the job scheduling system, in which case the first job won't run, either. The steps for the first job will look like this:

Separating the Backup from the Restore in SQL Server Agent

The first job step is the same, it simply does the backup. Here's the second job step:

EXEC sp_start_job @job_name = 'Test Restore of pubs';

Note that all you have to do is specify the job name for the second name. Also, make sure you set the database to be MSDB, because that's where the stored procedures controlling the job scheduling system are.

As for the second job? It simply has the steps 2 and 3 that were in the first example. Once that is set up, if there's a failure in the test restore, it won't affect the backup job plan. For instance, note that the backup job plan completed successfully.

Successful SQL Server Backup from SQL Server Agent

However, the restore did fail:

Failed SQL Server Restore from SQL Server Agent

And thus, if we're setting up notification, we would get a notification failure about the test restore and not about the backup.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, December 9, 2015 - 2:25:39 PM - K. Brian Kelley Back To Top (40225)

By using SQLCMD, you can specify a restore to any server you want (hence the -S switch). This assumes you're going to do it to a second server. Obviously, if you were restoring to the same server, there's no point in dropping the database afterwards. 

You would do the restore to a second server to verify the backup is good and to have an estimate of how long a restore takes to accomplish. Once those tasks are done, you don't need the database any more, so deleting it clears the way for the next restore. 

Wednesday, December 9, 2015 - 12:34:37 PM - SOCONFUSED Back To Top (40224)

I've been reading this for an hour and don't follow it at all. How are you restoring to a second (different) server?

Is pubs_RestoreTest a linked server?

And why would you drop the db after restoring? What would be the purpose of doing the restore if you are just deleting it?

Monday, June 30, 2014 - 9:59:40 AM - Paul Brewer Back To Top (32467)


Here is a 2 script framework (1 powershell, 1 stored procedure) that can be used automate SQL restores -

It offers various override options (data, log and backup file locations), uses CHECKSUM where possible, identifies historic backups when needed (IE previous to the last full, diff then logs)

The PoSh script can be scheduled as a SQL Agent job, it relies on the backup files being on a network share accessible from the standby.

Hope this helps, regards


Wednesday, December 12, 2012 - 10:10:08 AM - K. Brian Kelley Back To Top (20916)

sqlfriend, the job would be on the original server. However, depending on your environment, you might need to transfer the backup file over. The script would need to be in a location where the SQL Server Agent for the original server can access it.

Tuesday, December 11, 2012 - 9:07:45 PM - TimothyAWiseman Back To Top (20900)

Great Tip, thanks.

I woulc caution that with a larger database something like this could take up a lot of time, and depending on your configuration, a lot of bandwidth as well.

Tuesday, December 11, 2012 - 6:08:37 PM - sqlfriend Back To Top (20898)

Thanks Brian for this article, I like solution.

But I have  a question, are all the scripts and backup file , job saved in the original server?

Does the backup need to copy to test server?

Tuesday, December 11, 2012 - 5:13:42 PM - sqlrumble Back To Top (20896)

Hello, just to add my 2 pence worth, we restore full databases daily, for reporting, dev environments etc..  I use a linked server to the msdb database which i query and allows me to pick up the latest full backup and diff backup filename and path.  To ensure there are no users accessing the database, I use the following 'ALTER DATABASE <DBNAME> SET SINGLE USER WITH ROLLBACK IMMEDIATE' This drops all connections to the database.  

Tuesday, December 11, 2012 - 3:50:36 PM - K. Brian Kelley Back To Top (20888)

Right, I know you're turning xp_cmdshell back off, but for say, my environment, simply turning it on and making that configuration change is an audit point. That's why I suggested determining the backup file name outside of the T-SQL and passing it in as a result.

Tuesday, December 11, 2012 - 3:29:57 PM - Reuben S Back To Top (20887)

Hi Brian,

The stored procedure will restore the latest backup from the location being passed. The xp_cmdshell configuration option is being checked, enabled and finally disabled within the same procedure (see lines 117-130 & 476-489). I also made sure that for environments where the option is enabled, it remains enabled after the procedure completes execution.

The lack of a backup file name as an input parameter is by design since the said file name is variable due to differing database names and in some cases may contain a date & time value. The stored procedure can be called using Powershell or any other programming language that can execute T-SQL code.

The only thing I am not happy about in my stored procedure is that the data files will all be restored to the same location, but it was never a requirement, and it works!  The procedure has been restoring production databases to test environments for more than 8 months and works with SQL Server 2005, 2008 & 2008 R2. For the R2 version see the note about the TDEThumbprint column.


Tuesday, December 11, 2012 - 1:16:46 PM - K. Brian Kelley Back To Top (20882)

Rueben, looking at your stored procedure, you use xp_cmdshell. Granted, this is to list the file contents for a directory, but a lot of places have that specifically turned off. That would cause your script to fail. It would probably be better to allow the name of the backup to be passed in to the script. That way a DBA could use PowerShell or another mechanism to find the right backup and avoid this issue.


Tuesday, December 11, 2012 - 12:17:48 PM - Hugo Mendes Back To Top (20881)

Great post. We use something similar for restoring databases periodically onto a test server and we found that we had to include a step, prior to the restore, to kill any users that were connected. In your case, it's a test server and so users may not be connected, but you never know. Thanks again.

Tuesday, December 11, 2012 - 11:22:39 AM - Reuben S Back To Top (20880)

A T-SQL solution, encapsulated in a stored procedure, which will automatically restore a database from an existing backup can be found at: <a href=""></a>


Tuesday, December 11, 2012 - 11:20:42 AM - Jim VanBeek Back To Top (20879)

I modified something like this to personalize it:

Tuesday, December 11, 2012 - 11:03:33 AM - K. Brian Kelley Back To Top (20878)

Absolutely. PowerShell is always an option for automation. However, for those who aren't as comfortable with their PowerShell skills, this offers the "old school" alternative.

Tuesday, December 11, 2012 - 11:02:32 AM - Kevin Back To Top (20877)

Care to share the PS script, Jim? 



Tuesday, December 11, 2012 - 9:58:31 AM - Jim VanBeek Back To Top (20875)

An alternative is to create a PowershellScript. I have several that I use to backup my production database, copy it to my development computer, and restore it. It is quite simple to do and I have a batch file to call the script. You could also schedule it.



get free sql tips
agree to terms