In my previous post, Native SQL Server Backup Types and How-To Guide, I discussed the main types of native SQL Server backups and various backup options. Backups are critical to restoring databases quickly, but there isn’t much benefit to having backup files sitting around if you aren’t prepared and know when and how to perform the restores.
This article will give you a primer on restoring database backups, transaction log backups, along with the correct restore sequence needed for a series of backups. A restore sequence is the set of restores necessary to bring a database (or portion thereof) to the desired point in time.
Restoring a Full SQL Server Database Backup
All restore sequences must begin with restoring a full backup (most commonly a full database backup), and unless you’re trying to restore a database to an earlier point in time, this will be the most recent full database backup.
A full database backup contains all the data in the database at the time the backup was performed and enough of the transaction log to allow the restored database to be recovered, so it’s transactionally consistent to a certain point in time, which I’ll call the recovery point. In this case, the recovery point is when the full database backup process has completed reading the data from the database, not when the backup ended.
There are two parts to recovery:
- Ensuring changes to the database were committed by the recovery point are present in the restored database. This is known as redo.
- Ensuring changes to the database from transactions not committed by the recovery point are not present in the restored database. This is known as undo.
This brings me to the most important point to consider when restoring backup files: do you want to restore more than the full backup? This is important because the default option for any RESTORE command is to fully recover the database (the WITH RECOVERY option is implied) when the restore operation completes, i.e., running both redo and undo.
Once full recovery has completed, no further backups can be restored for the database. Because of this, it’s a best practice to use WITH NORECOVERY option on all RESTORE commands, which does the redo portion of recovery for the backup being restored, then forces the undo portion of recovery at the end of the restore sequence using the command:
Using the NORECOVERY option is also required when restoring a full database backup for configuring high availability features like log shipping or availability groups.
A restore can be performed using the GUI in SQL Server Management Studio or use T-SQL. My preference is using T-SQL, so I can have various restore procedures already scripted out.
As you saw above, the syntax for the RESTORE command is similar to the BACKUP command; you indicate what you want to restore, where to restore from (the backup device or devices), and any desired options. The command to restore a full backup is “RESTORE DATABASE,” and here are some common options:
- REPLACE – to overwrite any existing database
- STATS = X – to receive progress messages during the restore operation every X percent
- NORECOVERY – as discussed above, to allow multiple restore operations
- MOVE – to restore database files to somewhere other than the original location
The code below performs a full database restore process of a database named MyBigDatabase from a single backup device, overwriting any existing database, allowing more restores to occur, and progress messages every 10 percent:
If this is the only restore operation being performed, you could omit the NORECOVERY option or specify RECOVERY. However, I strongly advise you to get into the habit of always specifying NORECOVERY on any RESTORE command. If you accidentally leave it out and want to perform multiple restores, there’s no option but to restart the restore sequence.
Larger databases often have a full database backup spread across multiple files. In this case, the restore operation has to include a list of the various files. Here’s an example:
If you want to move the database files to new locations, each database file and its new location must be specified, as shown below:
You can find the list of files using the RESTORE FILELISTONLY command.
Restoring a Differential SQL Server Database Backup
If you remember from the backup post, it’s common for organizations to use differential backups to save backup storage space and reduce the time it takes to restore a database in the event of a disaster.
A differential database backup contains all the changes to the database since the most recent full database backup. And that’s an important point to stress: “since the most recent full database backup.” A differential backup can only be used in a restore sequence that starts with that specific full backup, and this is enforced using a GUID that resets each time a full database backup is performed.
If you try to restore a differential backup after restoring the wrong full backup, you’ll see this error message:
Msg 3136, Level 16, State 1, Line 8
This differential backup cannot be restored because the database hasn’t been restored to the correct earlier restoring state.
Msg 3013, Level 16, State 1, Line 8
RESTORE DATABASE is terminating abnormally.
The syntax for restoring a differential backup is exactly the same as for restoring a full backup, but you don’t need to specify options like REPLACE or MOVE, as those are already set by the restoration of the full backup.
Here’s an example of restoring a full backup and a differential backup, then ending the restore sequence:
Restoring Transaction Log Backups
For any business-critical SQL Server database, an organization should be performing periodic full backups (e.g., weekly) with transaction log backups at a frequent interval. Typically, I see this interval range from 5 minutes to 15 minutes, with 15 minutes being most common.
For each log backup restored in the restore sequence, the log records are copied from the backup file into the transaction log, and the redo portion of recovery is performed. The more log backups need to be restored to get the SQL Server database to the desired point in time, the longer the restore sequence will take. This is where differential database backups come in—they essentially replace all the log backups performed since the most recent full backup, and restoring a single differential backup is usually (not always) faster than restoring multiple log backups, essentially replaying all activity in the database.
For this reason, a typical organization will also perform regular differential backups, with a frequency somewhere between the full and log backups. I typically see weekly full database backups, daily differential database backups, and transaction log backups every 15 minutes.
To be able to restore log backups, you must first restore the full database backup with NORECOVERY. If you’re using differential backups, restore the most recent differential backup next, also with NORECOVERY.
Then you need to figure out which log backup to restore next. This is the log backup with the next set of log records after the last one contained in the data backup you just restored. You can find this information using the following query:
For the last data backup you restored, look at the last_lsn column in the output. Then find the log backup with the LSN in the range between first_lsn and last_lsn; that’s the first log backup to restore next.
Prior to any restore of a production database, you need to consider if a tail-log backup needs to be performed and added to the restore sequence. The tail-log backup would include any transactions on the database since the most recent automated transaction log backup. A tail-log backup is just a transaction log backup; you restore it like any other transaction log backup. It just happens to be the last one you restore.
Once you have the list of backups to restore, then you have your restore sequence. The syntax is slightly different for restoring log file backups, as you specify RESTORE LOG instead of RESTORE DATABASE. However, if you use the RESTORE DATABASE command on a log backup, SQL Server does the right thing.
Below is an example of a complete restore sequence:
Other Ways to Restore SQL Server Database Backups
I’ve covered the basics of a restore sequence, but many other, more complex restore options exist. Some examples are:
- Partially restoring a database to bring the most critical portion online as fast as possible
- Restoring a portion of a database (a filegroup, file, or set of individual pages) into an existing database to repair a damaged portion of it
- Restoring to a particular point in time or a particular point in the transaction log
- Restoring encrypted backups on a different server
- Using marked transactions to coordinate restoring a set of databases to the same point in time
- Using the WITH STANDBY option to temporarily get read-only access to the database during the restore sequence
Going into depth on these is beyond the scope of this post, but you can read more about all these in the Microsoft SQL Server documentation here.
Importance of Understanding the Restore Process
Having backups of your SQL Server databases is crucial for protecting the data assets of your organization. It’s just as important to understand how to restore your backups and validate their integrity by regularly restoring the database backups. If you’re using full, differential, and transaction log backups, you should be familiar with the process of constructing your restore sequence and performing it. A DBA should be comfortable performing all types of restores, so the recovery can go smoothly when a disaster happens.
SolarWinds® SQL Sentry is designed to support more actionable troubleshooting and optimization, so you can spend less time identifying issues and more time proactively improving database performance.
Tim is a Principal Consultant with SQLskills, and has worked in a variety of roles, including DBA, Lead DBA, and multi-department manager. He is a SQL Server MVP, has a whole collection of Microsoft and other industry certifications, and is very active and passionate in the SQL Community.
