Simplified Restores with SQL Server 2012 Recovery Advisor

Occasionally, a DBA may need to restore a database from a multiple backup files that originated from multiple servers. This requirement might arise, for example, in a database-mirroring configuration, where backups may be from either of the servers.

It can be a complex task. The backup history table in the msdb database for each instance will contain only information about the specific backups on that instance, and not on the other instance in the mirroring configuration. This means that the restore operation typically requires the DBA to collect backup header information from each of the backup files, using RESTORE DATABASE WITH HEADERONLY, and then stitch together the recovery sequence using the Log Sequence Numbers (LSNs) from each of the backups contained within the subset of selected backup files.

In order to ease this process, SQL Server 2012 introduces a "new feature" called the Database Recovery Advisor (in reality, it's a significant enhancement to the RESTORE DATABASE dialogs in SSMS, which have existed since SQL Server 2005).

The Muddy Waters of Multi-file Restores

The best way to try to ease the path of any multi-file restore operation, whether the files originate from one our multiple server, is to use clear and detailed naming for the backup files, which includes information like the database name, the backup type, and the date and time that the backup was taken. I'd cite Ola Hallengren's database maintenance scripts as one excellent example of this type of detailed naming.

However, even with such good naming conventions in place, it is still easy to encounter recovery failures, when we have to use file names as the only criteria to establish the correct sequence. For example,

• if COPY_ONLY backups, not required for the recovery operation, have occurred within the recovery period that were written to the same path as the standard backups using the same naming convention
• when several backups have been written to the same backup file, meaning that multiple required backups are in the same file

Further, as noted in the introduction, there are a number of cases, such as in a mirroring configuration, where a DBA might need to stitch together, and restore, backups from multiple servers in order to recover a database. If both of the mirroring partners are online and available, the DBA can use a script to read the backup history tables in msdb, from both of the servers, and so build the correct recovery sequence. However, this won't be possible in cases where we need to build the recovery sequence because of a partner failure, where the msdb database is unavailable for that node.

In SQL Server 2012, use of Availability Groups complicates still further the process of forming a complete recovery chain for a database, from existing backups on disk. With Availability Groups, it's possible, for example, to configure a read-only Secondary server as the preferred backup node, "offloading" transaction log backups from the Primary server. Depending on the configuration of the Availability Group (AG) environment, and the backup priority for each of the secondary replicas, it is possible to have a mix of backups taken from three or more nodes in the AG environment.

Again, it may be possible to construct the recovery scheme manually by querying each server, separately, but this is complicated and relies on the fact that no backup history cleanup had occurred on any of the nodes. The main aim of the Database Recovery Advisor (DRA) is to simplify this sort of multi-server, multi-file restore operation.

Database Recovery Advisor

When working with a database that is in an AG, with backups taken across different AG nodes, there will be a break in the log sequence numbers stored in the msdb backup history for the current node. In such a case, the DRA will display an error to the effect that it cannot use the msdb backup history data to create the restore plan.

Figure 1: Break in LSN chain message

The biggest big advantage of the DRA is that it has full support for UNC paths with file enumeration. Therefore, to avoid the sort of problem shown in Figure 1, we can write all backups for the AG environment to a shared UNC path, and the DRA can access the files from this location, and read the backup file headers, rather than msdb, to determine a restore plan, based on the selected backups. This eliminates the need for msdb backup history, and reduces the complexity of restore operations.

In such cases, it is necessary to use Device level restore to select the backup files from their appropriate backup locations, based on the specific backup configuration. For simplicity, Figure 2 shows a single folder containing all of the backups from the AG. However, you may have a backup configuration that writes the backup files to a separate folder, based on the physical server name of the node on which the backup occurred and, in such cases, you need to select the backup files based on the configuration in your specific environment.

Figure 2: Single folder for all backups

Either setup works for the DRA, as long as we select all of the backups for that database, from the file system location(s) in which they are stored. Having done this, the DRA will be able to build automatically the recovery steps for the database.

Figure 3: Backups from multiple folder paths

Having added all of the necessary backup files to the Select backup devices screen, as shown in Figure 3, the DRA begins analyzing the selected backups by reading the backup header information from the files. Once the DRA has read all of the backup headers, it analyzes the First and Last LSN information to build a full recovery plan for the database, based on the selected backups, which will provide the least amount of data loss, as shown in Figure 4.

Figure 4: Full recovery sequence with tail-log backup option

When an unbroken log sequence exists, the DRA will generate the restore plan, as shown in Figure 4. However, if a break in the log sequence exists, for example if someone took a log backup to a different location, then the DRA will not display a recovery plan, even a partial plan, and instead we'll see the "broken log chain" message from Figure 1.

If the DRA can generate a recovery plan, and the database exists on the target server for the restore operation, the DRA will add a tail log backup of the source database to the recovery steps, as shown in Figure 4, in the red box at the top of the window. However, it won't perform any checks to ensure that the log sequence numbers from the local database (the target for the restore operation) will work in the recovery plan, so it is necessary to verify manually that this is correctly going to restore as a part of the restore plan.

If this tail-log backup message appears, it is highly recommended that you review the backup Options page, as shown in Figure 5, to determine the location to which the tail-log backup will be written. If this tail-log backup forms part of the current in the log sequence chain, it may be needed for future recovery operations using this same log sequence.

Figure 5: Tail-log backup location

The Options page shows the tail-log backup location, which is the default backup location for the current server, and a filename that is a concatenation of the database name, the term LogBackup, and a date and time stamp.

As noted previously, when working with AGs, this log backup will be taken from the current replica (the one to which the UI is connected), not the primary replica. In order for this to work correctly, we must configure the backup preferences for the AG to allow for backups from secondary replicas (as was the case for this example).

Once ready, we can click OK and the DRA will automatically perform the restore operation. Alternatively, we can hit script the operation, by hitting the Script button at the top of the UI, and run it manually.

Once you are ready the can be handled by the Database Recovery Advisor automatically by clicking on OK, or through scripting the operation using. The script for our example restore operation, as generated by the DRA, is shown in Listing 1.

USE [master]
BACKUP LOG [ConfigDB] TO  DISK = N'\\SQL2K12-SVR1\SQLBackups\ConfigDB_LogBackup_2012-04-30_11-06-03.bak' WITH NOFORMAT, NOINIT,  NAME = N'ConfigDB_LogBackup_2012-04-30_11-06-03', NOSKIP, NOREWIND, NOUNLOAD,  STATS = 5
RESTORE DATABASE [ConfigDB] FROM  DISK = N'\\SQL2K12-SVR1\SQLBackups\ConfigDB\SQL2K12-SVR1_ConfigDB_FULL_20120430_100646.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [ConfigDB] FROM  DISK = N'\\SQL2K12-SVR1\SQLBackups\ConfigDB\SQL2K12-SVR2_ConfigDB_LOG_20120430_100801.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [ConfigDB] FROM  DISK = N'\\SQL2K12-SVR1\SQLBackups\ConfigDB\SQL2K12-SVR1_ConfigDB_LOG_20120430_101001.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [ConfigDB] FROM  DISK = N'\\SQL2K12-SVR1\SQLBackups\ConfigDB\SQL2K12-SVR3_ConfigDB_LOG_20120430_101200.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [ConfigDB] FROM  DISK = N'\\SQL2K12-SVR1\SQLBackups\ConfigDB\SQL2K12-SVR3_ConfigDB_LOG_20120430_101401.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [ConfigDB] FROM  DISK = N'\\SQL2K12-SVR1\SQLBackups\ConfigDB\SQL2K12-SVR1_ConfigDB_LOG_20120430_102201.trn' WITH  FILE = 1,  NOUNLOAD,  STATS = 5
GO

Listing 1: DRA-generated restore operation

Summary

The Database Recovery Advisor in SQL Server 2012 simplifies the complex task of having to piece together a recovery sequence from multiple backups.