How to Automate SQL Server Restores for a Test Server

By: K. Brian Kelley   |   Updated: 2012-12-11   |   Comments (16)   |   Related: > Restore

Problem

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.

Solution

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:

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

BACKUP DATABASE [pubs] 
TO DISK = N'c:\temp\backups\pubs_full.bak' 
WITH INIT, SKIP, CHECKSUM
GO

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.

RESTORE DATABASE pubs_RestoreTest
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',
RECOVERY;

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:

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

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:

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:

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.

However, the restore did fail:

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

• Learn how to auto-generate your restore scripts based on a directory of backups.
• Understand how to auto-generate your restore scripts after each backup.
• Consider using PowerShell to automate your restores.

About the author

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