The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob - Blog

 

Home

No Ads ever, except search!
Saturday, April 17, 2021 Login
Public

Blog posts for the month of July,2013.
Log backup failing because there is no current database backup7/15/2013 7:36:55 AM

This happens from time to time, and I thought I had it covered with a pretty slick lookup to see if a full database backup had been completed, and if not there was a break out to take a full backup.  This worked pretty well.

But it still failed from time to time.  Usually because one of the following:

• The recovery model had been changed without taking a full backup
• A SQL maintenance plan task had changed the recovery model (reindexing can cause this)
• A backup specifying TRUNCATE_ONLY had been run on the database
• The database was detached from another server and attached to the current server

I thought a try catch might work, but it turns out the error is thrown in such a way you can't trap it properly until the next version of SQL Server.

A little research shows that it is possible to test for this quite easily,

Detecting Error Message: BACKUP LOG cannot be performed because there is no current database backup

Now the problem is fixed.

Query is:

 

       select distinct db.[name]

       , Case

              When drs.last_log_backup_lsn is null

              then 1 --Needs full database backup

              Else 0 --Has Full Database Backup

       End as Type

       from master.sys.databases db

       inner join master.sys.database_recovery_status drs on db.database_id = drs.database_id

       Where db.[name] not in ('Master', 'Model', 'msdb', 'Tempdb')

       ORDER BY db.[name]

 


Blog Home