Differential backup question

Question

Post reply

Differential backup question

dgermundson

Hall of Fame

Points: 3175
More actions
July 24, 2003 at 12:16 pm

#160529

I'm doing a differential backup once a day and a full backup once a week. Example:
Sunday: Full backup
Monday: Diff backup
Tuesday: Diff backup
Let's say I want to restore the Tuesday version of the database after the tuesday backup. Question:
1) Should the differential backups use INIT? This would mean that the Tuesday backup contains the data changed since the full backup. Or do I not use INIT and have to apply both the monday and tuesday backups to restore.
In summary, do differential backups take the changes since the last FULL backup or FULL OR DIFFERENTIAL??
Thanks
Darren
Darren

Darren

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

quote:
In summary, do differential backups take the changes since the last FULL backup or FULL OR DIFFERENTIAL??

Last Full Backup.

dgermundson Hall of Fame Points: 3175 More actions · Answer 2

Thanks, so I can safely use INIT on the backup because I always will restore from the latest version in my scenario.

Darren

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 3

The INIT option specifies that all backup sets will be overwritten.

The NOINIT indicates that the backup set is appended to the specified backup device, preserving existing backup sets. NOINIT is the default.

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 4

Diferential backups, take the changes since the last full backup. Once you made a diff backup,

all the other differential backups you made, are useless.

You should backup the full backup to a backup device with init,

and backups all the diff backups to a diferent device also with init, to reduce disk space

Randy Rabin SSCommitted Points: 1512 More actions · Answer 5

Just a suggestion, but you may want to alternate between two backup targets, instead of using one file over and over. Reason is, if for some reason you have a catastrophic failure during the backup, you've lost both your database *and* your diff backup, and potentially several days worth of data. With two files, you've only lost at most one day.

e.g.

Sunday: full backup

Monday: diff backup to Backup1.DIF with init

Tues: diff backup to Backup2.DIF with init

Wed: diff backup to Backup1.DIF with init

etc.

Randy

Philip Kelley SSCertifiable Points: 6351 More actions · Answer 6

I would recommend you create each differential backup in it's own file (assuming your backing up to DISK) or it's own <whatever> assuming you're backing up to tape.

Let's say you have a Complete backup on Sunday and (early morning) differential backups for Monday, Tuesday, Wednesday, and Thursday. On Friday, you discover that something nasty has happened to your data, and further research shows that whatever went wrong occured mid-Tuesday. If you have all the backups, you're covered; if you have only your most recent backup (or two), you're in big trouble.

If you make daily backups to disk and then back those up to tape and use differential tape backups, you'll have the "original" backups on tape, but then you have to go to tape to get the file, which is inconvenient and burns time... particularly if you're storing them off-site... and that can be bad if you're talking Production data and hoards of angry users.

Of course, you have to balance online backup file retention against available disk (or tape) space, so your mileage may vary. [We're looking into SQL LiteSpeed just now--preliminary research implies at least a 50% reduction in backup file size across the board.]

Philip

gljjr SSChampion Points: 10080 More actions · Answer 7

I recently had a situation exactly like what Phillip described where I had to go back several days to get to some valid data. Luckily for me I have a full backup done nightly and diff backups done every 3 hours during the day. These 4 devices are then backed up to tape nightly. It was a fairly easy matter for me to restore the files from the tape and then restore the database. Had I just overwritten the files without having copies of them on the tapes I would have been unable to get back to the good data.

Gary Johnson

Microsoft Natural Language Group

DBA, Sr. DB Engineer

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.