Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

...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?

share|improve this question
    
This is expected - just run a full backup after the database is created. You can justify this by saying you're testing the full backup process. –  SuperCoolMoss Oct 14 '09 at 12:37
    
SCM - The backup isn't a problem... it's the inconvenience. I don't like to have to remember to do things (usually because I forget), and avoiding unnecesary job 'failures' is important for the reason I gave to NYSA. –  CJM Oct 14 '09 at 12:52
add comment

2 Answers 2

up vote 5 down vote accepted

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.

share|improve this answer
    
I'll try to remember to do a backup immediately - I can imagine a scenario where I ignore a backup failure email thinking it's because of a new DB - only to find at my cost that there was a genuine problem elsewhere. –  CJM Oct 14 '09 at 12:51
add comment

I put a flag on my list of DBS to back up that indicates that it has been backed up. The backup script won't do Txn backups until it equals 1. In my full backup script I set the flag to 1. Presto, no more failures.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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