(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.)

This myth is one of the most common and I've come across very few people who know the truth.

Myth #20: after breaking the log backup chain, a full database backup is required to restart it.

FALSE

A normal transaction log backup contains all the transaction log generated since the previous log backup (or since the first ever full backup if it's the first ever log backup for the database). There are various operations that will break the log backup chain – i.e. prevent SQL Server from being able to take another log backup until the chain is restarted. The list of such operations includes:

Here's an example script that shows you what I mean:

CREATE DATABASE LogChainTest;
GO
ALTER DATABASE LogChainTest SET RECOVERY FULL;
GO
BACKUP DATABASE LogChainTest TO DISK = 'C:\SQLskills\LogChainTest.bck' WITH INIT;
GO
BACKUP LOG LogChainTest TO DISK = 'C:\SQLskills\LogChainTest_log1.bck' WITH INIT;
GO
ALTER DATABASE LogChainTest SET RECOVERY SIMPLE;
GO
ALTER DATABASE LogChainTest SET RECOVERY FULL;
GO

Processed 152 pages for database 'LogChainTest', file 'LogChainTest' on file 1.
Processed 1 pages for database 'LogChainTest', file 'LogChainTest_log' on file 1.
BACKUP DATABASE successfully processed 153 pages in 0.088 seconds (14.242 MB/sec).
Processed 2 pages for database 'LogChainTest', file 'LogChainTest_log' on file 1.
BACKUP LOG successfully processed 2 pages in 0.033 seconds (0.341 MB/sec).

I created a database, put it into the FULL recovery model, started the log backup chain, and then momentarily bounced it into the SIMPLE recovery model and back to FULL.

Now if I try to take a log backup: 

BACKUP LOG LogChainTest TO DISK = 'C:\SQLskills\LogChainTest_log2.bck' WITH INIT;
GO

Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.

SQL Server knows that I performed an operation which means the next log backup will NOT contain all the log generated since the previous log backup, so it doesn't let  me do it.

The myth says that a full database backup is required to restart the log backup chain. In reality, all I need is a data backup that bridges the LSN gap. A differential backup will do:

BACKUP DATABASE LogChainTest TO DISK = 'C:\SQLskills\LogChainTest_Diff1.bck' WITH INIT, DIFFERENTIAL;
GO
BACKUP LOG LogChainTest TO DISK = 'C:\SQLskills\LogChainTest_log2.bck' WITH INIT;
GO

Processed 40 pages for database 'LogChainTest', file 'LogChainTest' on file 1.
Processed 1 pages for database 'LogChainTest', file 'LogChainTest_log' on file 1.
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 41 pages in 0.083 seconds (4.040 MB/sec).
Processed 1 pages for database 'LogChainTest', file 'LogChainTest_log' on file 1.
BACKUP LOG successfully processed 1 pages in 0.010 seconds (0.768 MB/sec).

This is really cool because you don't need to take a (potentially very large) full database backup to be able to continue with regular log backups.

If you have a backup strategy that involves file or filegroup backups as well as database backups, you can even restart the log backup chain after a single file differential backup! Take note, however, that to be able to restore that database, you'd need to have a data backup of each portion of it that bridges the LSN gap (i.e. a file or filegroup full or differential backup) but that's more complicated than I want to go into in this post.

Another myth bites the dust!

Related Posts