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:
- Creating a new database.
- Changing the recovery model to SIMPLE.
- Restoring/reverting a database from a snapshot.
- 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