Find if valid full backup exists for SQL Server Database
I’m wanting to find for a given database whether or not a valid full backup exists that can be used for differential or trans log backups. Of course it’s not enough that a full backup exists, it must be able to begin a chain. To this end I want to eliminate those backups created before a recovery model change, as well as copy only backups. TSQL only please. No GUI explanations.
One Solution collect form web for “Find if valid full backup exists for SQL Server Database”
I looked at the source from Ola Hallengren’s backup script and came up with this:
SELECT CASE WHEN last_log_backup_lsn IS NULL THEN 0 ELSE 1 END AS log_backup_possible FROM sys.database_recovery_status WHERE database_id = DB_ID('foobar');
A log backup can be taken if the last_log_backup_lsn is not null. Also, a differential backup is good enough to start taking log backups.
In other news, I alluded to Ola Hallengren’s excellent maintenance scripts above. Specifically, he has a parameter in the backup stored procedure (@ChangeBackupType) that takes care of all of this for you (so you don’t have to re-invent the wheel).