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:
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
- In the New Job dialog provide a name for the job and all applicable details (owner, category, description)
- In the Select a page pane click on the Steps tab and click on the New button to create a backup step
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'
- Click OK to add a step
- 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.
- Click the New button
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
- 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.
- Start the application, and in the main ribbon, click on the Restore button to bring up the Restore wizard:
In the first step of the wizard, specify the following:
- 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.
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
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
The only remaining step is to inspect the summary and finish the scheduling process
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