1

Playing around on my local sql server at home after an issue occurred at work. We have full backup jobs scheduled on Mondays only. I created a DB on Tuesday (when doffs and trans are scheduled). Wednesday morning I received SQL agent email noticed stating the backup jobs failed specifically for the new DB. After reviewing the logs I read "The Job was invoked...There is no DB to backup," before the Senior DBA told me to do a manual full backup and then it will run. Why is this?

2
  • Think about it this way. To restore a database, you need to restore a full database backup, and then any log backups that you need to apply. If the system were to allow you to take a log backup before any full backups have been taken, what would be the point? What use is a log backup with no full backup to apply it to? This would be like keeping the keys to a car you totaled. Apr 10, 2015 at 23:38
  • The log only stores changes made to the database since the last full backup (or since database creation if no full backup has yet been taken). Apr 11, 2015 at 7:01

1 Answer 1

0

do a manual full backup and then it will run. Why is this?

This is because any database you create with full recovery model is always in pseudo simple recovery mode until you take a full database backup and this is where a log backup chain can be establish successfully.

You can easily check if a database is in pseudo simple recovery mode using DBCC DBINFO () WITH TABLERESULTS** --> check for field dbi_dbbackupLSN with value of 0:0:0 (0x00000000:00000000:0000). This means that the log chain has not been established.

Paul Randal has a TSQL script and Edwin Sarmiento has a PowerShell script that will tell you if a database is in pseudo simple recovery or not.

You can use the scripts from above links to first check if the database is in pseudo simple recovery and if the database is then execute a full backup else run diffs or log backups.


** DBCC DBINFO is undocumented.

Not the answer you're looking for? Browse other questions tagged or ask your own question.