Search code examples
sql-servert-sqldatabase-backups

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.

Solution

  • 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).