Microsoft SQL Server databases support three recovery models – Simple, Bulk-logged and Full Recovery – each targeted to provide unique recovery capabilities. For this post, it is assumed that the reader is aware about the various recovery models.

When restoring a database under the FULL recovery model, the recovery involves restoring the databases from upto 3 different kinds of backups:

• Full Database Backup
• Differential Database Backup
• Transaction Log Backup

As can be understood from the post on backup timelines and understanding the database restore process from Pinal Dave, a.k.a SQLAuthority (B|T), the fastest way to restore a database in the full recovery model is:

• Restore the last known, good full backup
• Restore the latest of all known differential backups
• Restore each transaction log, in sequence backed up after from the last differential backup was performed

This works fine when we have a handful of transaction log files to restore. But, it becomes difficult to identify which particular transaction log should be restored when we have a collection of log files (on a SAN storage, tape or any other form of storage) – some of which would be invalid based on the backups already restored. The question therefore is:

Can a given transaction log backup be restored on a database?

To help answer this question, Microsoft SQL Server provides a built-in stored procedure – sp_can_tlog_be_applied (http://msdn.microsoft.com/en-us/library/ms187769.aspx).

This system stored procedure can be used to determine if a given transaction log backup can be restored on a database in recovery (If the database is not in recovery, no backups can be restored). Allow me to present an example to demonstrate the usage of this function.

Preparing the backup set

The following set of scripts performs the following tasks:

1. Create a test database
2. Create a test table within the newly created database
3. Change the database recovery model to FULL
4. Take a full backup of the database
5. Modify some data and take two log backups

USE master;
GO
--1. Create database
CREATE DATABASE LogVerificationDB
ON PRIMARY (NAME = PrimaryFile,
            FILENAME = 'C:SQL DatabasesLogVerificationDBPrimaryFile.mdf'
           )
LOG ON (NAME = LogFile,
        FILENAME = 'C:SQL DatabasesLogVerificationDBLogFile.ldf'
       );
GO

--2. Create test data
USE LogVerificationDB;
GO
CREATE TABLE LogVerificationTable (ObjectId INT, ObjectName VARCHAR(200));
GO

--3. Change recovery mode to FULL
ALTER DATABASE LogVerificationDB SET RECOVERY FULL;
GO

USE master;
GO
BACKUP DATABASE LogVerificationDB 
TO DISK = 'C:SQL DatabasesLogVerificationDBLogVerificationDB_Full.bak';
GO

--4. Make some modifications
USE LogVerificationDB;
GO
INSERT INTO LogVerificationTable (ObjectId, ObjectName)
SELECT so.object_id, so.name
FROM sys.objects AS so;
GO

--5. Take a log backup
USE master;
GO
BACKUP LOG LogVerificationDB
TO DISK = 'C:SQL DatabasesLogVerificationDBLogVerificationDB_Log1.bak';
GO

--6. Make some more modifications
USE LogVerificationDB;
GO
INSERT INTO LogVerificationTable (ObjectId, ObjectName)
SELECT so.object_id, so.name
FROM sys.objects AS so;
GO

--7. Take another log backup
USE master;
GO
BACKUP LOG LogVerificationDB
TO DISK = 'C:SQL DatabasesLogVerificationDBLogVerificationDB_Log2.bak';
GO

sp_can_tlog_be_applied in action

Now, let us assume that the database crashed and needs to be restored from the available backups. To do so, I manually dropped the database.

--08. Drop the test datbase
USE master;
GO
ALTER DATABASE LogVerificationDB SET SINGLE_USER;
GO
DROP DATABASE LogVerificationDB;
GO

First, I restore the full backup with NORECOVERY specified, indicating that other backups are yet to be restored.

--09. Restore WITHOUT Recovery
USE master;
GO
RESTORE DATABASE LogVerificationDB
FROM DISK = 'C:SQL DatabasesLogVerificationDBLogVerificationDB_Full.bak'
WITH  FILE = 1,  
      NOUNLOAD ,  
      STATS = 10,  
      NORECOVERY ,  
MOVE N'PrimaryFile'    TO N'C:SQL DatabasesLogVerificationDBPrimaryFile.mdf',
MOVE N'LogFile' TO N'C:SQL DatabasesLogVerificationDBLogFile.ldf';
GO

--09A. Check status
SELECT state_desc, state, user_access_desc FROM sys.databases WHERE name = 'LogVerificationDB';
GO

Now that the full backup has been restored, we know that we can start restoring the differential and/or the transaction log backups. But, which one of the backup file goes first? We use the sp_can_tlog_be_applied system stored procedure to check which one of Log backup #1 and #2 can be applied at this stage.

--10. Check if the transaction logs can be applied
--    Since we restored the database with no recovery, transaction log backups can be applied
--    However, can Log Backup #01 be applied?
DECLARE @canTxLogBeApplied BIT = 0;
EXEC sp_can_tlog_be_applied 
         @backup_file_name = 'C:SQL DatabasesLogVerificationDBLogVerificationDB_Log1.bak', 
         @database_name = 'LogVerificationDB', 
         @result = @canTxLogBeApplied OUTPUT;

SELECT @canTxLogBeApplied AS CanTransactionLog#1BeApplied;
GO

--11. Check if the transaction logs can be applied
--    Since we restored the database with no recovery, transaction log backups can be applied
--    However, can Log Backup #02 be applied?
DECLARE @canTxLogBeApplied BIT = 0;
EXEC sp_can_tlog_be_applied 
         @backup_file_name = 'C:SQL DatabasesLogVerificationDBLogVerificationDB_Log2.bak', 
         @database_name = 'LogVerificationDB', 
         @result = @canTxLogBeApplied OUTPUT;

SELECT @canTxLogBeApplied AS CanTransactionLog#2BeApplied;
GO

Because the transaction log backup #2 is newer than #1, it cannot be applied directly. Log backup #1 has to be applied first. This can be confirmed in the screen-shot below where it is seen that the sp_can_tlog_be_applied returns a 1 for Log backup #1, but a 0 for backup #2.

Conclusion:

As can be seen from the example above, a simple check using sp_can_tlog_be_applied can help administrators determine the sequence in which transaction log backup files are to be restored on a database.

Did you know?

Did you know that sp_can_tlog_be_applied was available in SQL Server 2000 also? I will admit that I did not know about it before researching for this blog.

Further Reading

• sp_can_tlog_be_applied (BOL Link)
• Restore Sequence and Understanding NORECOVERY and RECOVERY (Link)
• Backup Timeline and Understanding of Database Restore Process in Full Recovery Model (Link)

Until we meet next time,

Be courteous. Drive responsibly.