Day 19 of 31 Days of Disaster Recovery: How Much Log Can a Backup Log

It’s day 19 of my 31 Days of Disaster Recovery series, and today I want to talk about how much log is in a backup file. A common misconception is that when you restore a backup, you get an exact copy of the database as it was when it was backed up. That’s mostly true, but there are exceptions to that. For example, if you restore a database to a new server, the TRUSTWORTHY property gets reset as does replication or CDC configurations and objects unless you specify to keep them as part of the restore. Another common one is that the entire transaction log is in the backup. In fact, the backup only has as much a log as is required to bring the database to a consistent state upon recovery. I’m going to demonstrate just how much log a backup can log (or contains).

If you missed any of the earlier posts in my DR series, you can check them out here:

31 Days of disaster Recovery
1. Does DBCC Automatically Use Existing Snapshot?
2. Protection From Restoring a Backup of a Contained Database
3. Determining Files to Restore Database
4. Back That Thang Up
5. Dealing With Corruption in a Nonclustered Index
6. Dealing With Corruption in Allocation Pages
7. Writing SLAs for Disaster Recover
8. Resolutions for All DBAs
9. Use All the Checksums
10. Monitoring for Corruption Errors
11. Converting LSN Formats
12. Extreme Disaster Recovery Training
13. Standard Backup Scripts
14. Fixing a Corrupt Tempdb
15. Running DBCC CheckTable in Parallel Jobs
16. Disaster Recovery Gems From Around The Net
17. When are Checksums Written to a Page
18. How to CHECKDB Like a Boss

Examine the Log

For this demonstration, I’m going to be using undocumented function fn_dump_dblog() and undocumented trace flag 2537. The function is similar to other commands with which you may be familiar, DBCC LOG() and fn_dblog(). You can find some unofficial documentation on fn_dump_dblog() on the blog of fellow Certified Master Dimitri Furman (blog) here: Reading database transaction log with fn_dump_dblog(). Also, Paul Randal (blog|@PaulRandal) has blogged about using this function. I generally prefer using DBCC LOG() or fn_dblog() because the parameters to pass in are more manageable. The really cool thing about fn_dump_dblog() though is that it can be used to view the transaction log inside of a backup file.

Additionally, trace flag 2537 can be used in conjunction with any of the three log reader functions mentioned above to include the inactive portion of the log file when you view it. I have been told that this trace flag used to be 2536. Not sure exactly when it changed from 2536 to 2537, so if you try this on an older version than SQL Server 2008, you may need to use trace flag 2536 instead.

For this demo, I’m going to start out by creating a new database and switching to it:

Use master;

-- Create Database
Create Database TestBackups;
Go

-- Switch to database
Use TestBackups;
Go

Next step is to run a manual CHECKPOINT to ensure that the log is clear. At this point, we should only see 2 or 3 entries (depending on your version of SQL Server) for the checkpoint operation. I’ll use the fn_dump_dblog() function to look at the active log and verify. On a few occasions, the checkpoint will run prior to the database logging everything it needs to do for the initial creation. If you see a lot of transactions in the log, just run this step again.

-- Clear the tran log
Checkpoint;
Go

-- Log entries should be only the checkpoint entries, 3 records
Select *
From fn_dump_dblog(null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null);
Go

Next I create a table and fill it with some data inside of a marked transaction. When you name a transaction, you can find the start of the transaction in the transaction log by looking for the name in the Transaction Name column of the output. After running the named transaction, I will query the log file for the count of records in the log file (lots) and also for the entry for the named transaction (to show that it’s there).

-- Insert some data into a new table in a named transaction
Begin Tran Tran1 With MARK 'Tran 1'
Select *
INTO dbo.MasterFiles
From sys.master_files;
Commit
Go

-- Lots of log entries now
Select count(*)
From fn_dump_dblog(null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null);

-- Including our named transaction
Select *
From fn_dump_dblog(null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null)
Where [Transaction Name] = 'Tran1';
Go

Next, I will CHECKPOINT the database manually again to clear the log. Then a dump of the log will show that we are back to only 2 or 3 entries for the CHECKPOINT command. Next, I will enable trace flag 2537 and requery the log for the count of records and for the named transaction. Now that we can also see the inactive portion of the log, we see that there is still a large number of transactions in the log including the named transaction.

-- Clear the tran log again
Checkpoint;
Go

-- Log entries should be only the checkpoint entries, 3 records
Select *
From fn_dump_dblog(null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null);

-- Enable trace flag 2537 to see all log entries (active and inactive)
DBCC TraceOn(2537);

-- Lots of inactive log entries
Select count(*)
From fn_dump_dblog(null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null);

-- Including our named transaction
Select *
From fn_dump_dblog(null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null)
Where [Transaction Name] = 'Tran1';

DBCC TraceOff(2537);
Go

Next I switch to master database, back up the database, and then use fn_dump_dblog() to query the log file contained inside of the backup file we just created. We see that there is only a small number of records in the backup file and our named transaction is not among them.

-- Switch to master
Use master;

-- Backup the database
Backup Database TestBackups
    To Disk = 'C:\bak\TestBackups.bak'
    With Init;
Go

-- Check log entries in backup
Select count(*)
From fn_dump_dblog(null, null, N'Disk', 1, N'C:\bak\TestBackups.bak',
        null, null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null);

-- What about our named transaction?
Select *
From fn_dump_dblog(null, null, N'Disk', 1, N'C:\bak\TestBackups.bak',
        null, null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null)
Where [Transaction Name] = 'Tran1';
Go

And then finally, I drop the database because I’m done.

Use TestBackups;
Alter Database TestBackups Set Single_User With Rollback Immediate;
Use master;
Drop Database TestBackups;
Go

Summary

Earlier today, I was lurking around #sqlhelp on Twitter, and someone pointed out the blog post by Dimitri that I linked to earlier. It struck me that fn_dump_dblog() would be a good way to demonstrate that the log file in the backup file did not include the entire log file, just the part required to bring the database to a consistent state. Be sure to work through the demo code above or download the script below:

Demo script in zip format: TranLogInBackup.zip (1 KB)