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?

closed as off-topic by Jack Douglas Jun 28 '15 at 4:17

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Jack Douglas
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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. – Aaron Bertrand Apr 10 '15 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). – Colin 't Hart Apr 11 '15 at 7:01
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.