Horkay Blog
The postings on this site are my own and do not represent my Employer's positions, advice or strategies.
Monday, 15 July 2013

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]

 

Monday, 15 July 2013 06:36:55 (Central Standard Time, UTC-06:00) | Comments [0] | SQL Server#
Search
Popular Posts
Unpatched Vulnerabiltiy discovered ...
Spring Fornicator brewed...
SQL Server Security, not where it n...
DTA - Failed to initialize MSDB dat...
Check the Uptime of a Windows Serve...
Recent Posts
Archive
June, 2017 (2)
May, 2017 (2)
April, 2017 (2)
March, 2017 (1)
February, 2017 (1)
December, 2016 (2)
October, 2016 (2)
September, 2016 (1)
August, 2016 (1)
July, 2016 (1)
March, 2016 (2)
February, 2016 (3)
December, 2015 (4)
November, 2015 (6)
September, 2015 (1)
August, 2015 (2)
July, 2015 (1)
March, 2015 (2)
January, 2015 (1)
December, 2014 (3)
November, 2014 (1)
July, 2014 (2)
June, 2014 (2)
May, 2014 (3)
April, 2014 (3)
March, 2014 (1)
December, 2013 (1)
October, 2013 (1)
August, 2013 (1)
July, 2013 (1)
June, 2013 (2)
May, 2013 (1)
March, 2013 (3)
February, 2013 (3)
January, 2013 (1)
December, 2012 (3)
November, 2012 (1)
October, 2012 (1)
September, 2012 (1)
August, 2012 (1)
July, 2012 (4)
June, 2012 (3)
April, 2012 (1)
March, 2012 (3)
February, 2012 (3)
January, 2012 (4)
December, 2011 (3)
October, 2011 (2)
September, 2011 (2)
August, 2011 (8)
July, 2011 (4)
June, 2011 (3)
May, 2011 (3)
April, 2011 (1)
March, 2011 (2)
February, 2011 (3)
January, 2011 (1)
September, 2010 (1)
August, 2010 (2)
May, 2010 (2)
April, 2010 (3)
March, 2010 (1)
February, 2010 (4)
January, 2010 (1)
December, 2009 (3)
November, 2009 (2)
October, 2009 (2)
September, 2009 (5)
August, 2009 (4)
July, 2009 (8)
June, 2009 (2)
May, 2009 (3)
April, 2009 (9)
March, 2009 (6)
February, 2009 (3)
January, 2009 (8)
December, 2008 (8)
November, 2008 (4)
October, 2008 (14)
September, 2008 (10)
August, 2008 (7)
July, 2008 (7)
June, 2008 (11)
May, 2008 (14)
April, 2008 (12)
March, 2008 (17)
February, 2008 (10)
January, 2008 (13)
December, 2007 (7)
November, 2007 (8)
Links
Categories
Admin Login
Sign In
Blogroll