Monday, March 12, 2012

Question about DIFFERENTIAL backups

Our production backup schedule is, FULL backup once a month and a DIFFERENTIAL backup every day.

We are starting the FULL and DIFFERENTIAL backups using something similar to

FULL Backup
backup database @.DB to disk = @.BackupFile

DIFFERENTIAL backup
backup database @.DB to disk = @.BackupFile WITH DIFFERENTIAL

A full backup was done on 10/6 and the DB size was 48GB. Since then we have been doing DIFFERENTIAL backups. I recently looked at the DIFFERENTIAL backup directory and found something I think is interesting.

10/07/2005 04:00 AM 8,604,160 prodDB_Diff_200510070400.BAK
10/08/2005 04:00 AM 1,144,320 prodDB_Diff_200510080400.BAK
10/09/2005 04:00 AM 1,134,080 prodDB_Diff_200510090400.BAK
10/10/2005 04:00 AM 21,185,024 prodDB_Diff_200510100400.BAK
10/11/2005 04:00 AM 7,119,360 prodDB_Diff_200510110400.BAK
10/12/2005 04:00 AM 163,669,504 prodDB_Diff_200510120400.BAK
10/13/2005 04:00 AM 14,743,040 prodDB_Diff_200510130400.BAK
10/14/2005 04:00 AM 120,875,520 prodDB_Diff_200510140400.BAK
10/15/2005 04:00 AM 1,216,000 prodDB_Diff_200510150400.BAK
10/16/2005 04:00 AM 5,139,968 prodDB_Diff_200510160400.BAK
10/17/2005 04:00 AM 4,277,760 prodDB_Diff_200510170400.BAK
10/18/2005 04:00 AM 2,778,624 prodDB_Diff_200510180400.BAK
10/19/2005 04:01 AM 750,575,104 prodDB_Diff_200510190400.BAK

My understanding of DIFFERENTIAL backups from books online is
"Specifies the database or file backup should consist only of the portions of the database or file changed since the last full backup"

MY Question:
Assuming I am understanding this correctly, all DIFFERENTIAL backups are independent of one another. If a failure happens, all I have to do is restore the latest FULL backup and then
restore the latest DIFFERENTIAL backup.

So, I would think the sizes of the backup files should be in ASCENDING order. Why is the size of the backup file from 10/15/2005 (1,216,000 bytes) smaller than the backup file from 10/14/2005 (120,875,520 bytes).

Assuming a crash happens on 10/15 5AM, I would restore the full backup from 10/6 and then restore the DIFF. backup from 10/15 4AM (Which will apply approx.. 1,216,000 bytes of data). So where has the data in the backup file from 10/14/2005 gone (120,875,520 bytes)?

ThanksНou mistaken
Look for keyword "Differential Database Backups"

"A differential database backup records only the data that has changed since the last database backup."
Draw attention - "last database backup". No full backup.|||From BOL:

DIFFERENTIAL

Specifies the database or file backup should consist only of the portions of the database or file changed since the last full backup. A differential backup usually takes up less space than a full backup. Use this option so that all individual log backups since the last full backup do not need to be applied. For more information, see Differential Database Backups and File Differential Backups.

Note During a full database or differential backup, SQL Server backs up enough of the transaction log to produce a consistent database when the database is restored.

Are you doing a lot of updates during the time it takes to do the backups?|||Thanks so much for your response..

Right, your quote from BOL is exactly what I read. It has FULL backup written all over it.

Are you sure it is
""A differential database backup records only the data that has changed since the last database backup."

rather than what BOL says it is

"Specifies the database or file backup should consist only of the portions of the database or file changed since the last full backup"

I wouldn't say lots of updates but the DB is being used.|||You have the right idea about restoring the database in your situation. Just 2 restores, and one recovery, no matter what day you go down.

I am also puzzled by the wild size differences in there. This isn't something strange like the destination of a log-shipping instance, is it? I could see full backups on the source database causing this behavior.

No comments:

Post a Comment