SQL 2K Nightly Differentials

[wbodger]

So, I came across a data 'mishap' today that occurred on Friday (yeah, 5 days ago on a secondary table). Which meant that I had to go to my backups, but lo and behold I find out that the Sysadmin is not grabbing my nightly differentials. Which made me look into my maintenance plan (which, unfortunately I did not setup). What I know is that IT is doing a full backup of my backup drive twice a week. My question is, with the default differentials setup, is the differentials file reset each time a full backup is done and then just renamed with the date of the latest differential added? So, say a full backup was performed on the 16th, then they grabbed the differential from the 19th (but there was only the one differential file, not one for each day), would that hold all changes from the 16th to the 19th?

And then as I setup this up going forward, if I set the nightly differential up to overwrite and set the expiration period for that backup set as 7 days, will it create a differential file for each day? What I would like to have is a differential for each day between full backups, is that possible.

wb

 

[Mich]

Differential backups backup data since the last FULL backup. You can have a thousand diff backups since your last full bakcup and only the last diff will be applied. This is in contrast to EVERY incremental backup needing to be applied. A full restore is one full backup plus one (the latest) differential plus any trans logs needed.

I've always preferred differential backups for "relatively" small databases.

Does that answer your question?

-If it ain't broke, break it and make it better.

 

[wbodger]

Kind of. So, I run a full backup on sunday night, then run my differential every other night. If I realize on Friday that something happened to my data on WEdnesday (say one table got corrupted). how would I restore to Tuesday night's data set if the differentials are additive?

 

[Mich]

They aren't additive in that sense. They only add since the last full backup and not on top of each previous differential. Here's how I've used diff backups in the past,

Sunday - Full backup
Monday - Diff #1
Tuesday - Diff #2
Wednesday - Diff #3
and so on.

If I want to restore to Wednesday I restore Sunday's full plus Wednesday's diff (plus any applicable trans logs...going to quit mentioning this step). If I want to restore to Tuesday I restore Sunday's full plus Tuesday's diff.

Better answer your question?

-If it ain't broke, break it and make it better.

 

[wbodger]

So, when I look into the directory where the differentials are kept, I only see one differential file, dated yesterday. How do you achieve those distinct differential files?

wb

 

[Mich]

Ah, your setup is overwriting the diff file each night.

You'll to configure your backup to write a new file each night. An easy, albeit time consuming, way to do this is to create a diff backup job for each night of the week and name it diff_monday, diff_tuesday, and so on.

-If it ain't broke, break it and make it better.

 

[wbodger]

Ahh... So far as I can figure, the only other option would be to backup that nightly differential to a separate file every night. In the absence of another 'cleaner' method, it looks like I will create 6 separate nightly diffs that each expire after 7 days, thanks for the help!

Hey, one more question along those lines. Should I stop the log shipping during those backup times, or maybe just during the full backup?

wb

 

[Mich]

You don't have to expire each file, just overwrite it every week. That way every week you'll have a full on Sunday followed by a diff for each night of the week. Cuts down some of the hassle.

You can leave your log shipping enabled. It won't interfere with your backups and if you start, stop, start, stop your shipping it will get squirrelly on you.

-If it ain't broke, break it and make it better.

 

[wbodger]

Sounds good, thanks for the help!