(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.
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:
- Switching from the FULL or BULK_LOGGED recovery models into the SIMPLE recovery model
- Reverting from a database snapshot
- Performing a BACKUP LOG using the WITH NO_LOG or WITH TRUNCATE_ONLY (which you can’t do any more in SQL Server 2008+)
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!
14 thoughts on “A SQL Server DBA myth a day: (20/30) restarting a log backup chain requires a full database backup”
Great post (loving the series btw).
And, I hate to be an ass (okay, sometimes I enjoy it – just not here)… but I worry that some folks will read the title of this, and not bother to look at your explanation. And then there will be potential probs as people get complacent with the notion that they don’t need to reset the LSN when they break the backup chain.
Hi Mike – I know – I thought about that but these people can’t be helped :-)
I usually say ‘full backup’ on the forums, simply cause it’s easier. Just lazy I guess.
I switched my database recovery model from SIMPLE to FULL and then took FULL backup and a TRN backups afterwords. This is to set up mirroring.
However when I am restoring the trn backups, getting as error of “This log cannot be restored because a gap in the log chain was created. Use more recent data backups to bridge the gap”
Do I have to take the FULL backup with WITH INIT option? Please advise. Thanks!
No – what you say you’re doing should work perfectly. There must be another log backup that you’re missing, or the full backup you restored first was from while the database was in SIMPLE.
Thanks, Paul. Great to see your reply.
I followed same way I changed recovery model and took FULL backup. Somehow that did not work and then I tried my luck by taking diff backup and then again TRN backup. when I restored it in the sequence it worked. I must have done something weird before.
Paul ,may I know why DIFFERENTIAL backup can “bridges the LSN gap”?
Because it contains the net effect of all the changes described by the log backups over the same time (containing all the missing LSNs).
I have a Full Backup and differential backups.
I lost some differential backup and it seems the LSN was broken in those lost files.
Eg: Full backup was taken on 24thJune
Avaible diff backups start from 22July – today
When I restore the the latest diff backup – getting an ERROR – LSN broken
I’m assuming you have a question on what to do… if you restore your most recent full backup, and then the most recent differential backup and it’s giving an error, you’ll need to keep going backwards in time until you find a combination that works. Then fix your backup strategy so this doesn’t happen again. There’s no shortcut option here.
Backup scenario below
What happens if the log after the first diff gets corrupted? According to your article, you can restore the full and diff, but everything after that is lost. Now you can bridge the gap of corrupted logs by creating a diff or full, but how does SQL know to do that? Is it a manual process? Ideally, SQl would know the backup logs are broken and make a full or diff to bridge that gap.
I’m just having a hard time understanding the log chain and how that works with full or diffs. I’ve read that logs start after the first full, but techdev says it’s independent of full or diffs. How is it related but not at the same time? I would really like to understand log chains better.
How does SQL know what to restore since logs are not based on full or diffs? Logs might have transactions that span before or after full/diff backups.
Super confused IT guy
If the first diff is corrupt, your restore sequence is full then all the logs. A diff backup just lets you skip all the log backups between the most recent full and that diff backup.
SQL Server never performs backups automatically – you need to tell it do to it. It has no way of knowing that a backup is corrupt.
Yes, you can’t perform a log backup until you’ve performed the first full backup, but after that data backups have no effect on log backups.
You need to think of the log backup chain as the series of log backups that allow you to restore to a desired point in time. A restore sequence always starts with a full backup, then maybe a diff backup, then all the log backups necessary to get to the desired point in time.
Check out my article on TechNet Magazine https://technet.microsoft.com/en-us/library/2009.07.sqlbackup.aspx and I’m working on a Pluralsight course on backups too. But you can watch the Transaction Log Backups module of my Logging Pluralsight course at https://www.pluralsight.com/courses/sqlserver-logging.
If you have a Full, log, log, full, log, log and something happens to your ‘2nd full’ can you just use the first full and all the subsequent log files to get current? Or would there be a gap?
Yes, full backups do not break the log backup chain.