Video Screencast Help

SQL Server backup sets

Created: 28 Nov 2011 | 3 comments

I have two sql backup jobs. One for full backup and one for diff backup. My intent was for each full backup to start a new media file and all subsequent diffs to append to that same media file until the next full backup which should create a new file again with its coresponding diffs. This is how the media files should look when I run them in this order: Full1, Diff1, Diff2, Diff3, Full2, Diff4, Diff5, Diff6, Full3, Diff7, Diff8, Diff9

File1 [Full1, Diff1, Diff2, Diff3]

File2 [Full2, Diff4, Diff5, Diff6]

File3 [Full3, Diff7, Diff8, Diff9]

But to my surprise I get this:

File1 [Full1, Diff1, Diff2, Diff3, Diff4, Diff5, Diff6, Diff7, Diff8, Diff9]

File2 [Full2]

File3 [Full3]

This is not what I wanted because now I can't expire File1 which holds Diffs that need to be paired up with the full backups from File2 and File3. I need each backup media file to be self contained without dependencies to other files so they can be expired properly.

Any ideas how to achieve this?

Comments 3 CommentsJump to latest comment

pkh's picture

You got to set your AP in such a way that it expires after the last differential for the first full backup.  For example, if you do you full backups on Sun and your differential backups on Mon-Fri, then you should set your AP to 6 days.

If you are backing up to disk, then you should use overwrite for all your jobs and let them use a separate media file.  There is no advantage in append to a disk media.  When you append to a media, the OPP gets extended.

Dude16's picture

I am saving to file but the reason I wanted the full and its matching diffs to be in one file is so they expire together. If they are separate files then there will be a window of time where a full backup will be overwritten while the diffs matching it are still hanging around completely useless.

What is AP? I am guessing its the append period? Is this the same as the propection period? Where do I set the append period?

Obviously setting the protection period can not work since I want to be able to keep these generations for a lot longer than the amount of time between full backups. 

pkh's picture

AP is the append period which determines how long you can append to the media.  This is part of the media set properties like the OPP.