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, to 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 it 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 complete SQL Server database restore and the file/page restores is the following:

  • Complete database restore performs full restore and might take 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 afterwards. 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 3rd backup set on the device (FILE=3) and the differential database backup is the 5th 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

Create a scheduled database restore in SQL Server by using ApexSQL Backup

Another way to create and schedule database restore job in SQL Server is to use ApexSQL Backup – a SQL Server backup and restore management tool. ApexSQL Backup is a tool that helps user schedule a database restore job via an intuitive wizard, and also allows one to see all jobs history, schedules and outcomes, or raise an instant email alert on a job fail/success.

ApexSQL Backup is a tool that enables management and automation of the backup and restore jobs while preserving the backup chain for an easy point-in-time restoration – database rollback.

  1. Start the application, and in the main ribbon, click on the Restore button to bring up the Restore wizard:

  2. In the first step of the wizard, specify the following:

    1. Select a SQL Server that hosts the database
    2. Select an existing database for backup, or enter name for a new database
    3. Choose whether to forcefully close any existing connections to the database that might block a restore job

  3. Click on the Next button to advance to the Source step of the wizard.

    Choose whether to restore from the specific backup types, or from a folder scan.

    1. If a specific database backup is selected, it is required to select a file via the file explorer

    2. In case that the Scan folder option is selected, it is required to specify a folder path, and optional to add a filename pattern, and include subfolders:

  4. Click Next to advance to the next step of the wizard – Options. Here, it is required to specify a restore type, as well as locations for the data and log file folders, as well as the post-restore database verification. Click Next to advance to the scheduling process

  5. In this step of the wizard, the user needs to choose whether he would like to restore a SQL Server database immediately or to schedule a restore job. If the later one is selected, the Schedule wizard dialog will appear, and allow the user to choose a job frequency, a period, and a time range

    It is also possible to set up an automatic email notification when the scheduled job is completed

  6. The only remaining step is to inspect the summary and finish the scheduling process

  7. With this, the backup job schedule is created, and a backup will occur in accordance to the schedule’s parameters. A created schedule is now displayed in the Schedules view, with other existing schedules, from where it can be tracked, or alternatively removed:

Furthermore, when a scheduled restore job occurs, it can be tracked in the Activities tab

April 8, 2015