…at least I think so.
I recently revamped my backup routine on my development server. I created a maintenance plan for user databases that did some weekly cleanup tasks, daily full backups and transactionlog backups at key points throughout the day.
All has been working well until today, when a received an email notifying me that the lunchtime backup had failed. The log viewer didn't reveal much, other than that a certain number of DBs were backed up success fully, before ending the log entry with:
[snipped] Source: ... The package execution fa... The step failed.
Not exactly revealing
It occurred to me that the only change to previous attempts was that I'd created a new (small and unremarkable) database this morning.
I restarted the SQL Server Agent, and tried manually re-running the (transaction log backup) step but it failed again.
However, running the full backup step (thus creating the first full backup for my new DB) worked – furthermore, re-running the transaction log backup step again also worked.
It appears that the transaction log backup struggled because of the lack of a full backup or previous transaction log backup.
Is this the expected behaviour? On the one hand, at a primitive level this sort of makes sense, but really, I'd expect it to handle such a scenario more gracefully.
If so, is there a way to automatically handle this? It won't happen that often, but I can't be relied to remember to backup the DB when I create it, especially since it's only in the early stages of development.
If not, what is the likely cause of this behaviour? Can I avoid it?
Best Answer
Yes, this is expected behavior. In order for the transaction logs to be useful, there needs to be a full backup to use as a starting point for rolling forward.
Don't worry about it too much. If you create a new database, the first day you will get that error. The log backups for the other DBs will succeed, as you saw, but that error will just make it LOOK like the step failed when in reality only the one DB failed. Then, that night (or whenever), it will do the full backup. After that, the trans log backups will be successful for the new DB. Basically, you would have one day or errors when you create a new DB until the full backup job runs.