Differential backup question

[wbodger]

So, I am setting up maintenance plans for our databases and I notice that in SQL Server 2000, sp4, using the Maintenance Plan wizard, there is no option for doing differential backups. So, I went to the Backup section and setup a new backup device and setup a differential backup to that device. However, I can only see to create one file name and then append to that file each time I do a differential backup. Is this the best way to do it, or is there something else that I am missing?

Thanks for any help you can give me.

Willie

 

[ptheriault]

That is how I have them set up. You don't want your differential backups in many files.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[wbodger]

OK, thanks. I wasn't sure and figured better to confirm than wonder.

Willie

 

[wbodger]

One more question on this. Are Differential backups smart enough to reset themselves every time a complete backup is done? For instance, if my complete backup runs every Sunday at 2 AM, then does that 'reset' the differential backup file? I didn't see anyplace within the differential setup to define when to reset the BAK file. Thanks.

Willie

 

[ptheriault]

If you tried to restore a differential backup that is out of sequence it will fail. ARe you backing up to devices? if so, init your differential device after you run a full backup.


- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[wbodger]

It appears that I only have the option of initializing if I overwrite the data every night. Are Differentials comprehensive/cumulative? If I run a full backup on Sunday morning, then differentials Sunday, Monday and Tuesday nights, do I need the Sunday and Monday differentials to do a complete restore thru Tuesday night or can I just use the complete and Tuesday's differential?

 

[ptheriault]

a differential backup coppies all changed pages since the last full backup. So you only have to restore your most recent differential backup.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[wbodger]

OK, thank you for the clarification. I was working under a false assumption, I think I've got it straightened out now.

Willie