1

I have setup a job that runs every night and makes a full backup of all databases on the server. During the day, another job makes differential backups every few hours.

The problem comes when a new database is created. The differential backup job fails every time, until the full backup job runs for the first time since the database was added. After that, everything runs as expected, at least until another new database is added.

Of course, a differential backup doesn't make sense when there's no full backup yet, and I'm assuming that's why I'm getting an error.

Question is, is there something I can do do prevent this, preferably something that won't require manual action every time a new database is added?

Sir Code-A-Lot
  • 113
  • 4
  • 2
    `Of course, a differential backup doesn't make sense when there's no full backup yet, and I'm assuming that's why I'm getting an error.` Yup, good assumption. – HopelessN00b Oct 09 '12 at 11:22

2 Answers2

3

First question: why take differentials so often? If you need that level of recoverability, set your databases to full recovery and take log backups.

But to your original question, re-write your backup procedure to detect if there's no full backup for that database and if there isn't, perform a full backup. Backup information can be found in msdb in the backup* tables (backupset, backupmediaset, etc).

Ben Thul
  • 3,024
  • 17
  • 24
  • `why take differentials so often?` Because if you can, it's a good idea, especially if you've got people running around in the DBs mucking things up and dropping tables. `You're going to lose 2 hours of data when I restore` is usually well received; `You're going to lose all your data since last night` is not. – HopelessN00b Oct 09 '12 at 15:18
  • 1
    Which is why I suggested that if you need that level of recoverability that you use log backups to accomplish what you're trying to do. – Ben Thul Oct 09 '12 at 15:27
  • @BenThul Diffs can be taken regardless of the recovery model. If there are bulk loads in the environment and the recovery model is not full, Diffs could offer a significant advantage over the full recovery/tran log backup scenario. Additionally, recovery is much simpler with diffs. All you need to restore is the last full and the last diff. Much simpler than trying to recover with a full and 40 tran backups. On a tier 2 or 3 database, the cost of point in time recovery may outweigh the benefits. – brian Oct 12 '12 at 13:41
  • @brian: Yep... all good points. I always ask though because people seem to think that differential backups back up everything since the last differential when they really back up everything since the last full. So, if you're doing multiple differentials in a day, you're backing up the same data over and over again. – Ben Thul Oct 12 '12 at 19:34
2

Yes, you can script a full backup to be run at database creation time, or add in some code to create full backups of newly databases into the start of your differential backups job.

Or you could ignore the error(s), which might be hard, depending on how many there are.

Which action is preferable would depend on factors you haven't clued us into, so we can't say, but you should be able to figure out if it's worth it to script up something to backup your new databases ahead of the differential backup job or not.

HopelessN00b
  • 53,795
  • 33
  • 135
  • 209