SQL Server backup sets
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]
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?