Broken Backup Chains

There are a handful of activities that have broken my SQL Server backup routines. Sometimes a DIFFERENTIAL (or LOG) backup would fail because there was no FULL backup. Other times, a LOG backup would fail because the log chain was broken. Some of those activities include:

1. Creating a new database.
2. Changing the recovery model to SIMPLE.
3. Restoring/reverting a database from a snapshot.
4. Any other activity that breaks the transaction log chain.

Until recently, I had a rudimentary check in place to avoid the "never had a FULL backup" scenario. A simple query was all it took:

SELECT TOP(1) *
FROM msdb.dbo.backupset s
WHERE s.type = 'D'
AND s.is_copy_only = 0
AND s.database_name = '@DBName';

In my DIFFERENTIAL backup and LOG backup routines, I could run that query to see if a FULL backup had ever been taken, take a FULL backup as needed, and proceed. It worked well, but it wasn't bullet-proof. If a database was dropped and a new database with the same name was created, I had a problem. My query would verify the existence of a FULL backup for a different database that was the same in name only. An edge case, for sure. But it still broke my code. I had noticed the database_guid column in msdb.dbo.backupset and figured I could use that instead of the database name. But there's no matching column in master.sys.databases. More on this in a bit...

The broken log chain was something I never even tried to address. This would come back to haunt me many times. I had ALTER DATABASE alerts configured and got emails letting me know if a recovery model had been changed. So at least I had some lead time to try and head off an inevitable LOG backup failure. But this was a manual process that didn't sit well with me.

Thanks to Mike Fal's (b|t) blog post, I now have a solution for the broken log chain problem: check the value of last_log_backup_lsn in the master.sys.database_recovery_status DMV. If it is NULL, the log chain is broken and a LOG backup attempt will fail. Thank you, Mike!

I found the MSDN documentation unclear, so I did some testing. My conclusions are that column last_log_backup_lsn is NULL for any database that has never had a FULL backup, any database with the SIMPLE recovery model, and any database whose recovery model was switched from FULL (or BULK LOGGED) to SIMPLE and back again. Re-establishing the log chain by way of a FULL BACKUP (or DIFFERENTIAL BACKUP, if available) populated the last_log_backup_lsn column.

I also discovered master.sys.database_recovery_status has a database_guid column! This allowed me to fix my edge case problem with databases that were dropped and recreated with the same name. This query now tells me if a database has ever had a FULL backup:

SELECT TOP(1) *
FROM msdb.dbo.backupset bs
JOIN master.sys.database_recovery_status s
 ON s.database_guid = bs.database_guid
WHERE bs.database_name = '@DBName'
AND bs.type = 'D'
AND bs.is_copy_only = 0
AND bs.server_name = @@SERVERNAME