Knowledge Base

Scheduling SQL Backup restore jobs

Category: How do I?
Date: 30 Jan 2009
Product: SQL Backup
SQL Backup does not support the creation of restore jobs on the SQL Agent schedule as it does for scheduled backups.

Currently there isn't a way to set up a scheduled restore job using the SQL Backup GUI. However, this is something that the development team are considering for future versions. You can create scheduled restore jobs that utilize SQL Backup but it requires some manual steps.

You need to create a SQL Server Agent Job that runs a SQL Backup restore script.

Open the SQL Backup GUI and run through the manual restore wizard, choosing all of the required options to restore the backup. When you get to the last step of the wizard click, the script tab and copy the script. Open SQL Server Management Studio/Enterprise Manager, navigate to the server that you're restoring to and expand the SQL Server Agent node. Right click on the Jobs folder and create a new job. Create a job step in the job and paste the code that you copied from the SQL Backup restore wizard into the step. Schedule the job to run at the interval that you require.

If you want the SQL Backup agent to be informed of any errors with the restore job, you'll need to make sure that the script in the job step is encapsulated in some error handling code. You can see the code that is needed in the following knowledge base article - http://www.red-gate.com/supportcenter/Content.aspx?p=SQL%20Backup&c=knowledgebase\SQL_Backup\KB200707000075.htm