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.

  • Dynamic pivot table
  • How to get current connected database file path?
  • How to search with multiple criteria from a database with SQL?
  • How to calculate the total number of pair under particular parent according to pattern 1:2,1:1 day to day maximum up to 5 pair caping daily
  • SQL Update Table By Joining Same Table
  • Eliminating Special Characters From a SQL Server Query
  • 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).

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.