How to schedule a database backup restore in SQL Server

When recovering from a SQL Server database failure, a database administrator needs to restore a set of SQL Server database backups in a logically correct and meaningful restore sequence. With this in mind, the goal is to devise a disaster recovery strategy by creating a solid backup plan, as well as a proper database restore plan in SQL Server. This article will describe 2 different solutions for creating and scheduling a database restore in SQL Server.

When creating a database restore plan, the first thing to keep in mind is that there are 3 different restore scenarios in a SQL Server:

• Complete database restore – the whole database is restored by restoring a full database backup, all differential backups and all subsequent log backups
• File restore – a set of files or a data file is restored without restoring the entire database

• Page restore – restores one or more damaged pages (An unbroken chain of log backups must be available)
  Note: Page restore is not available when database is in a simple recovery model

The main difference between the complete SQL Server database restore and the file/page restores is the following:

• Complete database restore performs full restore and might take a substantial amount of time in comparison to other 2 scenarios
• During the files or pages restore process, the other (unaffected) data in the database will remain online and fully operational during the restore process

RECOVERY and NORECOVERY options

Regardless of how data is restored, before a database can be recovered, the SQL Server Database Engine checks and ensures that the whole database is logically consistent. This means that it will not be possible to recover a file and bring it online if it has not been rolled-forward far enough to be consistent with the rest of the database.

Database roll-back is controlled by the RESTORE statement through the RECOVERY and NORECOVERY options:

• RECOVERY (the default) indicates that after the roll-forward of the current backup is completed, the roll-back will be performed immediately afterward. Recovering the database requires that the entire set of data being restored (the roll-forward set) is consistent with the database. If the roll forward set has not been rolled forward far enough to be consistent with the database and RECOVERY is specified, the Database Engine issues an error.

• NORECOVERY specifies that roll-back will not occur after the roll-forward completes. This allows roll forward to continue with the next statement in the sequence. In this case, the restore sequence can restore other backups and roll them forward.

Create a scheduled database restore in SQL Server by using a SQL Server Agent job

To automate and schedule a database restore with SQL Server Agent:

1. In the Object Explorer pane, navigate to the SQL Server Agent, expand the node, right click Jobs and select New job from the context menu

   

2. In the New Job dialog provide a name for the job and all applicable details (owner, category, description)
3. In the Select a page pane click on the Steps tab and click on the New button to create a backup step

4. In the backup step, provide the step name and add Transact-SQL script (T-SQL)

   To restore a full SQL Server database (from the AdventureWorks2012Backups logical backup device) use the following script:

   RESTORE DATABASE AdventureWorks2012
      FROM AdventureWorks2012Backups;
   

   To restore a full and differential database backups use the following script:

   RESTORE DATABASE AdventureWorks2012
      FROM DISK = 'C:\Backups\AdventureWorks2012.bak'
      WITH FILE = 3
         NORECOVERY;
   RESTORE DATABASE AdventureWorks2012
      FROM DISK = 'C:\Backups\AdventureWorks2012.bak'
      WITH FILE = 5
         RECOVERY;
   

   Note: provided example restores a full database backup, followed by a restore of a differential backup from the same backup device/file. Restored full database backup is the 3^rd backup set on the device (FILE=3) and the differential database backup is the 5^th backup set on the device (FILE=5)

   To restore pages use the following script:

   RESTORE DATABASE AdventureWorks2012 PAGE='1:23, 1:107, 1:109, 1:349'
      FROM 'C:\Backups\AdventureWorks2012.bak' 
   

   To restore specific files or filegroups use the following script:

   RESTORE DATABASE AdventureWorks2012
      FILE = N'AdventureWorks2012_Data',
      FILEGROUP = 'new_customers',
      FROM DISK = N'C:\Backups\AdventureWorks2012.bak'
   

   

5. Click OK to add a step
6. The job will be created after click on the OK button in the New job dialog, but we will postpone it at this moment in order to create a schedule. To schedule a job, in the New Job dialog, click on the Schedules to open the job schedule dialog.
7. Click the New button

8. Provide a name for the schedule and specify the schedule type, an occurring frequency and the validity date. Click OK to create a schedule and close the dialog

   

9. Click on the OK button to finalize and create a job

At his moment, the job is created and can be located in the Object Explorer pane under the SQL Server Agent ➜ Jobs node.

To start the job, right click on it in the Object Explorer pane and select the Start job at step option

With this, the restore job is set to motion and will occur in relation to the predefined schedule

There are several cons to this method:

• The restore job and schedule wizards can be confusing and hard to manage
• There is no simple monitoring or overview of scheduled jobs or those that have already been completed