Problem

You have created a full backup of your database. Now you want to test the backup to ensure it is a good backup.

Solution

Use the RESTORE command to restore a database from a full database backup. Unlike a BACKUP operation, a RESTORE is not always an online operation—for a full database restore, user connections must be disconnected from the database prior to restoring over the database. Other restore types (such as filegroup, file, or page) can allow online activity in the database in other areas aside from the elements being restored. For example, if filegroup FG2 is getting restored, FG3 can still be accessed during the operation.

In general, you may need to restore a database after data loss because of user error or file corruption2 or because you need a second copy of a database or are moving a database to a new SQL Server instance.

The following is simplified syntax for the RESTORE command:

RESTORE DATABASE { database_name | @database_name_var } [ FROM < backup_device > [ ,. . . n ] ] [ WITH ] [Option Name ] [,. . . n]

The RESTORE DATABASE command also includes several options, many of which I’ll demonstrate in this chapter.

The first example in this recipe is a simple RESTORE from the latest backup set on the device (in this example, two backup sets exist on the device for the TestDB database, and you want the second one). For the demonstration, I’ll start by creating two full backups on a single device.

USE master
;
GO

Declare @BackupDate Char(8) = Convert(Varchar,GetDate(),112)
  ,@BackupPath Varchar(50);

Set @BackupPath = 'C:ApressTestDB_' + @BackupDate + '.BAK';

BACKUP DATABASE TestDB
TO DISK = @BackupPath;
GO
-- Time passes, we make another backup to the same device
USE master;
GO
Declare @BackupDate Char(8) = Convert(Varchar,GetDate(),112)
  ,@BackupPath Varchar(50);
Set @BackupPath = 'C:ApressTestDB_' + @BackupDate + '.BAK';
BACKUP DATABASE TestDB
TO DISK = @BackupPath;
GO

Now the database is restored using the second backup from the device (notice that the REPLACE argument is used to tell SQL Server to overlay the existing TestDB database).

USE master;
GO

Declare @DeviceName Varchar(50);

Select @DeviceName = b.physical_device_name
  From msdb.dbo.backupset a
  INNER JOIN msdb.dbo.backupmediafamily b
  ON a.media_set_id = b.media_set_id
  Where a.database_name = 'TestDB'
  And a.type = 'D'
  And Convert(Varchar,a.backup_start_date,112) = Convert(Varchar,GetDate(),112);
RESTORE DATABASE TestDB
FROM DISK = @DeviceName
WITH FILE = 2, REPLACE;
GO

This returns the following output (your results may vary):

Processed 4384 pages for database 'TestDB', file 'TestDB' on file 2.
Processed 2 pages for database 'TestDB', file 'TestDB_log' on file 2.
RESTORE DATABASE successfully processed 4386 pages in 10.742 seconds (3.189 MB/sec).

In this second example, a new database is created by restoring from the TestDB backup, creating a new database called TrainingDB. Notice that the MOVE argument is used to designate the location of the new database files.

USE master;
GO

Declare @DeviceName Varchar(50);

Select @DeviceName = b.physical_device_name
  From msdb.dbo.backupset a
  INNER JOIN msdb.dbo.backupmediafamily b
  ON a.media_set_id = b.media_set_id
  Where a.database_name = 'TestDB'
  And a.type = 'D'
  And Convert(Varchar,a.backup_start_date,112) = Convert(Varchar,GetDate(),112);
RESTORE DATABASE TrainingDB
FROM DISK = @DeviceName
WITH FILE = 2,
MOVE 'TestDB' TO 'C:ApressTrainingDB.mdf',
MOVE 'TestDB_log' TO 'C:ApressTrainingDB_log.LDF';
GO

This restore operation results in the following (your results may vary):

Processed 4384 pages for database 'TrainingDB', file 'TestDB' on file 2.
Processed 2 pages for database 'TrainingDB', file 'TestDB_log' on file 2.
RESTORE DATABASE successfully processed 4386 pages in 8.210 seconds (4.173 MB/sec).

In the last example for this recipe, the TestDB database is restored from a striped backup set. First, I create a backup set that will be used to perform the restore of a striped backup set.

USE master;
GO
/* The path for each file should be changed to a path matching one
That exists on your system. */
BACKUP DATABASE TestDB
TO DISK = 'C:ApressRecipesTestDB_Stripe1.bak'
  , DISK = 'D:ApressRecipesTestDB_Stripe2.bak'
  , DISK = 'E:ApressRecipesTestDB_Stripe3.bak'
  WITH NOFORMAT, NOINIT,
NAME = N'TestDB-Stripe Database Backup',
SKIP, STATS = 20;
GO

Now, I will perform the restore of the striped backup set.

USE master;
GO
/* You should use the same file path for each file as specified
in the backup statement. */
RESTORE DATABASE TestDB
FROM DISK = 'C:ApressRecipesTestDB_Stripe1.bak'
  , DISK = 'D:ApressRecipesTestDB_Stripe2.bak'
  , DISK = 'E:ApressRecipesTestDB_Stripe3.bak'
  WITH FILE = 1, REPLACE;
GO

This restore operation results in the following (your results may vary):

Processed 152 pages for database 'TestDB', file 'TestDB' on file 1.
Processed 1 pages for database 'TestDB', file 'TestDB_log' on file 1.
RESTORE DATABASE successfully processed 153 pages in 0.657 seconds (1.907 MB/sec).

How It Works

In the first example, the query began by setting the database to the master database. This is because a full RESTORE is not an online operation and requires that there be no active connections to the database that is being restored in order to run.

The RESTORE was for the TestDB database, and it overlaid the current database with the data as it existed at the end of the second backup set on the backup device created from this command.

Declare @BackupDate Char(8) = Convert(Varchar,GetDate(),112)
  ,@BackupPath Varchar(50);

Set @BackupPath = 'C:ApressTestDB_' + @BackupDate + '.BAK';

Prior to running the RESTORE command, I needed to query the msdb database to determine the name of the backup device since I created it dynamically based on the current date. The following query shows how to find the name of that backup device:

Declare @DeviceName Varchar(50);

Select @DeviceName = b.physical_device_name
  From msdb.dbo.backupset a
  INNER JOIN msdb.dbo.backupmediafamily b
  ON a.media_set_id = b.media_set_id
  Where a.database_name = 'TestDB'
  And a.type = 'D'
  And Convert(Varchar,a.backup_start_date,112) = Convert(Varchar,GetDate(),112);

Having retrieved the name of the backup device, I can now restore the database using the following RESTORE command while specifying over which database to restore:

RESTORE DATABASE TestDB

The next line of this example designated the location of the backup device.

FROM DISK = @DeviceName

The last line of this example designated which backup set from the backup device should be used to RESTORE from (you can use RESTORE HEADERONLY to see what backup sets exist on a backup device).

WITH FILE = 2, REPLACE

Any data that was updated since the last backup will be lost, so it is assumed in this example that data loss is acceptable and that data as of the last backup is desired. In the second example, a new database was created based on a RESTORE from another database. The example is similar to the previous query, only this time the MOVE command is used to designate where the new database files should be located (and the new database name is used as well).

MOVE 'TestDB' TO 'C:Apress TrainingDB.mdf,
MOVE 'TestDB_log' TO 'C:ApressTrainingDB_log.LDF'

RESTORE FILELISTONLY can be used to retrieve the logical name and physical path of the backed-up database.

In the last example of the recipe, the TestDB was restored from a striped backup set. FROM DISK was repeated for each disk device in the set.

USE master;
GO
RESTORE DATABASE TestDB
FROM DISK = 'C:ApressRecipesTestDB_Stripel.bak'
  , DISK = 'D:ApressRecipesTestDB_Stripe2.bak'
  , DISK = 'E:ApressRecipesTestDB_Stripe3.bak'
  WITH FILE = 1, REPLACE;
GO

In each of these examples, the database was restored to a recovered state, meaning that it was online and available for users to query after the redo phase (and during/after the undo phase). In the next few recipes, you’ll see that the database is often not recovered until a differential or transaction log backup can be restored.

Problem

You need to restore a database to a predetermined time that is after the last full backup.

Solution

You can perform transaction log restores in conjunction with a full backup by using the RESTORE LOG command. Transaction log restores require an initial full database restore, and if you’re applying multiple transaction logs, they must be applied in chronological order (based on when the transaction log backups were generated). Applying transaction logs out of order, or with gaps between backups, isn’t allowed. The syntax for restoring transaction logs is RESTORE LOG instead of RESTORE DATABASE; however, the syntax and options are the same.

For this demonstration, the TrainingDB created in the previous recipe will be used (if it doesn’t exist, we will create it).

USE master;
GO
IF NOT EXISTS (SELECT name FROM sys.databases
WHERE name = 'TrainingDB')
BEGIN
CREATE DATABASE TrainingDB;
END
GO
-- Add a table and some data to it
USE TrainingDB
GO
SELECT *
INTO dbo.SalesOrderDetail
FROM AdventureWorks2012.Sales.SalesOrderDetail;
GO

This database will be given a full backup and two consecutive transaction log backups.

USE master;
GO

Declare @BackupDate Char(8) = Convert(Varchar,GetDate(),112)
  ,@BackupPath Varchar(50);
Set @BackupPath = 'C:ApressTrainingDB_' + @BackupDate + '.BAK';

BACKUP DATABASE TrainingDB
TO DISK = @BackupPath;
GO
BACKUP LOG TrainingDB
TO DISK = 'C:ApressTrainingDB_20120430_8AM.trn';
GO
-- Two hours pass, another transaction log backup is made
BACKUP LOG TrainingDB
TO DISK = 'C:ApressTrainingDB_20120430_10AM.trn';
GO

The previous RESTORE examples have assumed that there were no existing connections in the database to be restored over. However, in this example, I demonstrate how to kick out any connections to the database prior to performing the RESTORE.

USE master;
GO
-- Kicking out all other connections
ALTER DATABASE TrainingDB
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

Next, a database backup and two transaction log backups are restored from backup.

USE master;
GO

Declare @DeviceName Varchar(50);

Select @DeviceName = b.physical_device_name
  From msdb.dbo.backupset a
  INNER JOIN msdb.dbo.backupmediafamily b
  ON a.media_set_id = b.media_set_id
  Where a.database_name = 'TrainingDB'
  And a.type = 'D'
  And Convert(Varchar,a.backup_start_date,112) = Convert(Varchar,GetDate(),112);
RESTORE DATABASE TrainingDB
FROM DISK = @DeviceName
WITH NORECOVERY, REPLACE;
RESTORE LOG TrainingDB
FROM DISK = 'C:Apress TrainingDB_20120430_8AM.trn'
WITH NORECOVERY, REPLACE
RESTORE LOG TrainingDB
FROM DISK = 'C:Apress TrainingDB_20120430_10AM.trn'
WITH RECOVERY, REPLACE

This results in the following (your results will vary):

Processed 5880 pages for database 'TrainingDB', file 'TrainingDB' on file 1.
Processed 3 pages for database 'TrainingDB', file 'TrainingDB_log' on file 1.
RESTORE DATABASE successfully processed 5883 pages in 8.140 seconds (5.645 MB/sec).
Processed 0 pages for database 'TrainingDB', file 'TrainingDB' on file 1.
Processed 2819 pages for database 'TrainingDB', file 'TrainingDB_log' on file 1.
RESTORE LOG successfully processed 2819 pages in 1.494 seconds (14.741 MB/sec).
RESTORE LOG successfully processed 0 pages in 0.072 seconds (0.000 MB/sec).

In this second example, I'll use STOPAT to restore the database and transaction log as of a specific point in time. To demonstrate, first a full backup will be taken of the TrainingDB database.

USE master;
GO
BACKUP DATABASE TrainingDB
TO DISK = 'C:ApressTrainingDB_StopAt.bak';
GO

Next, rows will be deleted out of the table, and the current time after the change will be queried.

USE TrainingDB;
GO
DELETE dbo.SalesOrderDetail
WHERE ProductID = 776;
GO
SELECT GETDATE();
GO

This query returns the following (your results will vary):

2012-04-30 22:17:11.563

Next, a transaction log backup is performed.

BACKUP LOG TrainingDB
TO DISK = 'C:ApressTrainingDB_20120430_2022.trn';
GO

This results in the following:

Processed 17 pages for database 'TrainingDB', file 'TrainingDB_log' on file 1. BACKUP LOG successfully processed 17 pages in 0.031 seconds (4.158 MB/sec).

The database is restored from backup, leaving it in NORECOVERY so that the transaction log backup can also be restored.

USE master;
GO
RESTORE DATABASE TrainingDB
FROM DISK = 'C:ApressTrainingDB_StopAt.bak'
WITH FILE = 1, NORECOVERY,
STOPAT = '2012-04-30 22:17:10.563';
GO

Next, the transaction log is restored, also designating the time prior to the data deletion.

RESTORE LOG TrainingDB
FROM DISK = 'C:ApressTrainingDB_20120430_2022.trn'
WITH RECOVERY,
STOPAT = '2012-04-30 22:17:10.563';
GO

The following query confirms that you have restored just prior to the data deletion:

USE TrainingDB;
GO
SELECT COUNT(*)
FROM dbo.SalesOrderDetail
WHERE ProductID = 776;
GO

This query returns the following:

228

How It Works

In the first example for this recipe, the TrainingDB database was restored from a full database backup and left in NORECOVERY mode. Being in NORECOVERY mode allows other transaction log or differential backups to be applied. In this example, two transaction log backups were applied in chronological order, with the second using the RECOVERY option to bring the database online.

The second example in the recipe demonstrated restoring a database as of a specific point in time. Point-in-time recovery is useful for restoring a database prior to a database modification or failure. The syntax was similar to the first example, only the STOPAT was used for both RESTORE DATABASE and RESTORE LOG. Including the STOPAT for each RESTORE statement makes sure that the restore doesn’t recover past the designated date.

Problem

As a part of your backup strategy, you have implemented differential backups. You now need to restore the database to a point in time after the last full database backup, taking advantage of the differential backups that have been taken.

Solution

You will use the RESTORE DATABASE command. The syntax for differential database restores is identical to full database restores, only full database restores must be performed prior to applying differential backups. When restoring the full database backup, the database must be left in NORECOVERY mode. Also, any transaction logs you want to restore must be done after the differential backup is applied, as this example demonstrates.

First, however, I’ll set up the example by performing a full, differential, and transaction log backup on the TrainingDB database.

USE master;
GO
BACKUP DATABASE TrainingDB
TO DISK = 'C:ApressTrainingDB_DiffExample.bak';
GO
-- Time passes
BACKUP DATABASE TrainingDB
TO DISK = 'C:ApressTrainingDB_DiffExample.diff'
WITH DIFFERENTIAL;
GO
-- More time passes
BACKUP LOG TrainingDB
TO DISK = 'C:ApressTrainingDB_DiffExample_tlog.trn';
GO

Now, I’ll demonstrate performing a RESTORE, bringing the database back to the completion of the last transaction log backup.

USE master;
GO
-- Full database restore
RESTORE DATABASE TrainingDB
FROM DISK = 'C:ApressTrainingDB_DiffExample.bak'
WITH NORECOVERY, REPLACE;
GO
-- Differential
RESTORE DATABASE TrainingDB
FROM DISK = 'C:ApressTrainingDB_DiffExample.diff'
WITH NORECOVERY;
GO
-- Transaction log
RESTORE LOG TrainingDB
FROM DISK = 'C:ApressTrainingDB_DiffExample_tlog.trn'
WITH RECOVERY;
GO

This returns the following (your results will vary):

Processed 5880 pages for database 'TrainingDB', file 'TrainingDB' on file 1.
Processed 3 pages for database 'TrainingDB', file 'TrainingDB_log' on file 1.
RESTORE DATABASE successfully processed 5883 pages in 0.443 seconds (2.831 MB/sec).
Processed 40 pages for database 'TrainingDB', file 'TrainingDB' on file 1.
Processed 2 pages for database 'TrainingDB', file 'TrainingDB_log' on file 1.
RESTORE DATABASE successfully processed 42 pages in 0.069 seconds (4.860 MB/sec).
RESTORE LOG successfully processed 0 pages in 0.070 seconds (0.000 MB/sec).

How It Works

Differential backups capture database changes that have occurred since the last full database backup. Differential restores use the same syntax as full database restores, only they must always follow a full database restore (with NORECOVERY) first. In this recipe, the database was initially restored from a full database backup, then followed by a restore from a differential backup, and then lastly a restore from a transaction log backup. The differential RESTORE command was formed similarly to previous RESTORE examples, only it referenced the differential backup file. On the last restore, the RECOVERY option was designated to make the database available for use.

Problem

You have a database with multiple filegroups. You need to restore one of the filegroups.

Solution

Restoring a file or filegroup uses virtually the same syntax as a full database restore, except you also use the FILEGROUP or FILE keyword. To perform a restore of a specific read-write file or filegroup, your database must use either a full or bulk-logged recovery model. This is required because transaction log backups must be applied after restoring a file or filegroup backup. In SQL Server, if your database is using a simple recovery model, only read-only files or read-only filegroups can have file/filegroup backups and restores.

To set up this recipe’s example, I will create the VLTestDB database if it doesn’t exist after which a filegroup backup is taken for the VLTestDB database.

USE master;
GO
If Not Exists (Select name from sys.databases where name = 'VLTestDB')
Begin
CREATE DATABASE VLTestDB
ON PRIMARY
  ( NAME = N'VLTestDB',FILENAME = N'c:ApressVLTestDB.mdf'
  ,SIZE = 4072 KB , FILEGROWTH = 0 ),
FILEGROUP FG2
  ( NAME = N'VLTestDB2', FILENAME = N'c:ApressVLTestDB2.ndf'
  , SIZE = 3048 KB , FILEGROWTH = 1024 KB )
  ,( NAME = N'VLTestDB3', FILENAME = N'c:ApressVLTestDB3.ndf'
  , SIZE = 3048 KB , FILEGROWTH = 1024 KB )
LOG ON
  ( NAME = N'VLTestDBLog', FILENAME = N'c:ApressVLTestDB_log.ldf'
  , SIZE = 1024 KB , FILEGROWTH = 10 %);
Alter DATABASE VLTestDB
Modify FILEGROUP FG2 Default;

END
GO

USE master;
GO
BACKUP DATABASE VLTestDB
FILEGROUP = 'FG2'
TO DISK = 'C:ApressVLTestDB_FG2.bak'
WITH NAME = N'VLTestDB-Full Filegroup Backup',
SKIP, STATS = 20;
GO

Time passes, and then a transaction log backup is taken for the database.

BACKUP LOG VLTestDB
TO DISK = 'C:ApressVLTestDB_FG_Example.trn';
GO

Next, the database filegroup FG2 is restored from backup, followed by the restore of a transaction log backup.

USE master;
GO
RESTORE DATABASE VLTestDB
FILEGROUP = 'FG2'
FROM DISK = 'C:ApressVLTestDB_FG2.bak'
WITH FILE = 1, NORECOVERY, REPLACE;
RESTORE LOG VLTestDB
FROM DISK = 'C:ApressVLTestDB_FG_Example.trn'
WITH FILE = 1, RECOVERY;
GO

This returns the following (your results may vary):

Processed 8 pages for database 'VLTestDB', file 'VLTestDB2' on file 1.
Processed 8 pages for database 'VLTestDB', file 'VLTestDB3' on file 1.
RESTORE DATABASE . . . FILE = <name> successfully processed
16 pages in 0.048 seconds (2.604 MB/sec).
Processed 0 pages for database 'VLTestDB', file 'VLTestDB2' on file 1.
Processed 0 pages for database 'VLTestDB', file 'VLTestDB3' on file 1.
RESTORE LOG successfully processed 0 pages in 0.062 seconds (0.000 MB/sec).

How It Works

Filegroup or file backups are most often used in very large databases, where full database backups may take too long to execute. With filegroup or file backups comes greater administrative complexity, because you’ll have to potentially recover from disaster using multiple backup sets (one per filegroup, for example).

In this recipe, the VLTestDB database filegroup named FG2 was restored from a backup device and left in NORECOVERY mode so that a transaction log restore could be applied. The RECOVERY keyword was used in the transaction log restore operation in order to bring the filegroup back online. In SQL Server Enterprise Edition, filegroups other than the primary filegroup can be taken offline for restores while leaving the other active filegroups available for use (this is called an ONLINE restore).

Problem

You have a database with multiple filegroups that needs to be recovered. You need to recover the primary filegroup in addition to any filegroups critical to the business based on a predetermined priority (you may recover certain filegroups at your leisure).

Solution

The PARTIAL command can be used with the RESTORE DATABASE command to restore secondary filegroups in a piecemeal fashion. This variation of RESTORE brings the primary filegroup online, letting you then restore other filegroups as needed later. If you’re using a database with a full or bulk-logged recovery model, you can use this command with read-write filegroups. If the database is using a simple recovery model, you can use PARTIAL only in conjunction with read-only secondary filegroups.

In this example, the VLTestDB is restored from a full database backup using the PARTIAL keyword and designating that only the PRIMARY filegroup be brought online (and with filegroups FG2 and FG3 staying offline and unrestored).

First, to set up this example, the primary and FG2 filegroups in the VLTestDB are backed up.

USE master;
GO
BACKUP DATABASE VLTestDB
FILEGROUP = 'PRIMARY'
TO DISK = 'C:ApressVLTestDB_Primary_PieceExmp.bak';
GO
BACKUP DATABASE VLTestDB
FILEGROUP = 'FG2'
TO DISK = 'C:ApressVLTestDB_FG2_PieceExmp.bak';
GO

After that, a transaction log backup is performed.

BACKUP LOG VLTestDB
TO DISK = 'C:ApressVLTestDB_PieceExmp.trn';
GO

Next, a piecemeal RESTORE is performed, recovering just the PRIMARY filegroup.

USE master;
GO
RESTORE DATABASE VLTestDB
FILEGROUP = 'PRIMARY'
FROM DISK = 'C:ApressVLTestDB_Primary_PieceExmp.bak'
WITH PARTIAL, NORECOVERY, REPLACE;
RESTORE LOG VLTestDB
FROM DISK = 'C:ApressVLTestDB_PieceExmp.trn'
WITH RECOVERY;
GO

The other filegroup, FG2, now contains unavailable files. You can view the file status by querying sys.database_files from the VLTestDB database.

USE VLTestDB;
GO
SELECT name,state_desc
FROM sys.database_files;
GO

This query returns the following:

How It Works

In this recipe, the VLTestDB was restored from a full backup, restoring just the PRIMARY filegroup. The WITH clause included the PARTIAL keyword and NORECOVERY so that transaction log backups can be restored. After the transaction log restore, any objects in the PRIMARY filegroup will be available, and objects in the secondary filegroups are unavailable until you restore them at a later time.

For very large databases, using the PARTIAL keyword during a RESTORE operation allows you to prioritize and load filegroups that have a higher priority, making them available sooner.

Problem

You have discovered that a few data pages have become corrupted in the database. You need to recover the corrupted pages.

Solution

SQL Server provides the ability to restore specific data pages in a database using a FULL or BULK_LOGGED recovery model via the PAGE argument. In the rare event that a small number of data pages become corrupted in a database, it may be more efficient to restore individual data pages than the entire file, filegroup, or database.

The syntax for restoring specific pages is similar to restoring a filegroup or database, only you use the PAGE keyword coupled with the page ID. Bad pages can be identified in the msdb.dbo.suspect_pages system table, can be identified in the SQL error log, or can be returned in the output of a DBCC command.

To set up this example, a full database backup is created for the TestDB database.

USE master;
GO
BACKUP DATABASE TestDB
TO DISK = 'C:ApressTestDB_PageExample.bak';
GO

Next, a restore is performed using the PAGE argument.

USE master;
GO
RESTORE DATABASE TestDB
PAGE = '1:8'
FROM DISK = 'C:ApressTestDB_PageExample.bak'
WITH NORECOVERY, REPLACE;
GO

This query returns the following:

Processed 1 pages for database 'TestDB', file 'TestDB' on file 1. RESTORE DATABASE . . . FILE = <name > successfully processed 1 pages in 0.470 seconds (0.016 MB/sec).

At this point, any differential or transaction log backups taken after the last full backup should also be restored. Since there were none in this example, no further backups are restored.

Next, and this is something that departs from previous examples, a new transaction log backup must be created that captures the restored page.

BACKUP LOG TestDB
TO DISK = 'C:ApressTestDB_PageExample_tlog.trn';
GO

This query returns the following:

Processed 2 pages for database 'TestDB', file 'TestDB_log' on file 1. BACKUP LOG successfully processed 2 pages in 0.840 seconds (0.014 MB/sec).

To finish the page restore process, the latest transaction log taken after the RESTORE. . .PAGE must be executed with RECOVERY.

RESTORE LOG TestDB
FROM DISK = 'C:ApressTestDB_PageExample_tlog.trn'
WITH RECOVERY;

How It Works

In this recipe, a single data page was restored from a full database backup using the PAGE option in the RESTORE DATABASE command. Like restoring from a FILE or FILEGROUP, the first RESTORE leaves the database in a NORECOVERY state, allowing additional transaction log backups to be applied prior to recovery.

Problem

You want to find any backups that have been created that are not used in your RESTORE process.

Solution

Use the sys.database_recovery_status catalog view. Multiple recovery paths are created when you recover a database from backup using point-in-time recovery or when you recover a database without recovering the latest differential or chain of log backups. When there are backups created that you do not use in your RESTORE process, you create a fork in the recovery path.

This recipe demonstrates how to use the sys.database_recovery_status catalog view to get information about a database with more than one recovery path. In the first step, I will create a new database and give it a full database backup, create a table and some rows, and finish up with a transaction log backup.

USE master;
GO
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = 'RomanHistory')
BEGIN
CREATE DATABASE RomanHistory;
END
GO
BACKUP DATABASE RomanHistory
TO DISK = 'C:ApressRomanHistory_A.bak';
GO
USE RomanHistory;
GO
CREATE TABLE EmperorTitle
(EmperorTitleID int NOT NULL PRIMARY KEY IDENTITY(1,1), TitleNM varchar(255));
GO
INSERT Into EmperorTitle (TitleNM)
  VALUES ('Aulus'), ('Imperator'), ('Pius Felix'), ('Quintus'),
BACKUP LOG RomanHistory
TO DISK = 'C:ApressRomanHistory_A.trn';
GO

Next, I’ll query the sys.database_recovery_status catalog view to get information about the database at this point (column aliases are used to shorten the names for presentation in this book).

USE msdb;
GO
SELECT LastLSN = last_log_backup_lsn ,Rec_Fork = recovery_fork_guid
  ,Frst_Fork = first_recovery_fork_guid ,Fork_LSN = fork_point_lsn
FROM sys.database_recovery_status
WHERE database_id = DB_ID('RomanHistory'),
GO

This query returns the following (your values will vary):

Notice that the first_recovery_fork_guid and fork_point_lsn columns are NULL. This is because I have not created a fork yet in my recovery path. The last_log_backup_lsn tells me the LSN of the most recent log backup, and the recovery_fork_guid shows the current recovery path in which the database is active.

Next, I will perform a few more data modifications and another transaction log backup.

USE RomanHistory;
GO
INSERT Into EmperorTitle (TitleNM)
  VALUES ('Germanicus'), ('Lucius'), ('Maximus'), ('Titus'),
GO
BACKUP LOG RomanHistory
TO DISK = 'C:ApressRomanHistory_B.trn';
GO

I’ll now go ahead and RESTORE the database to a prior state (but not to the latest state).

USE master;
GO
RESTORE DATABASE RomanHistory
FROM DISK = 'C:ApressRomanHistory_A.bak'
WITH NORECOVERY, REPLACE;
RESTORE DATABASE RomanHistory
FROM DISK = 'C:ApressRomanHistory_A.trn'
WITH RECOVERY, REPLACE;
GO

Now if I reissue the previous query against sys.database_recovery_status, I will see that both the fork_point_lsn and first_recovery_fork_guid columns are no longer NULL.

USE msdb;
GO
SELECT LastLSN = last_log_backup_lsn ,Rec_Fork = recovery_fork_guid
  ,Frst_Fork = first_recovery_fork_guid ,Fork_LSN = fork_point_lsn
FROM sys.database_recovery_status
WHERE database_id = DB_ID('RomanHistory'),
GO

This query returns the following (your results will vary):