question

ramesh 1 avatar image
ramesh 1 asked

sql server 2008 differential backup issue

hi team, i have a issue these days ,we have a databases of 35GB in size full backup, when we create a differential backup,its size grows upto 23 GB, i have verified the backup set using restore verifyonly from disk ='
sql-server-2008backupdifferential-backup
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
Cyborg avatar image
Cyborg answered
A differential backup creates a copy of all the pages in a database modified after the last database backup. So the size really depends with the period of Last full backup and the recent Differential backup. In a nutshell it is Ex: you had a full backup on Jan 1st around 10 GB imagine you had a differential backup on Jan 15 then the size of your differential backup is from Jan 1st to Jan 15 say it comes 2 GB For Feb 1st again u had a diff backup then the size of differential backup will be from 1st of jan to 1st of Feb(Remember the differential backups are not Incremental backups) i.e From your last full backup. In a nutshell the size of a differential backup depends on the amount of data that has changed since the base. Generally, the older a base is, the larger a new differential backup will be. A specific differential backup captures the state of changed extents at that time that backup is created. I suggest you to go throught [Differential Backup MSDN][1] article [1]: http://msdn.microsoft.com/en-us/library/ms345448.aspx
6 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ramesh 1 avatar image ramesh 1 commented ·
we have full backup of 35 GB size, when i take a differential backup it consumes 22 GB ,there after i take full backup it takes 35.5GB,
0 Likes 0 ·
Cyborg avatar image Cyborg commented ·
Ramesh, can you post the DIFF backup command that you are using?
0 Likes 0 ·
ramesh 1 avatar image ramesh 1 commented ·
BACKUP DATABASE [db_name] TO DISK = N'U:\Backups\db_name\db_name_backup_2010_11_02_030013_6000051.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'db_name-Differential Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO
0 Likes 0 ·
Cyborg avatar image Cyborg commented ·
i am little concerned with the NOINIT Option, that will append backup with the existing backup set if any.
0 Likes 0 ·
ramesh 1 avatar image ramesh 1 commented ·
but in maintenance plan we create the backup name as dbname+date_time.bak how does it append while there is no file to append
0 Likes 0 ·
Cyborg avatar image Cyborg commented ·
so what is your full backup name and differential backup name? are they same?
0 Likes 0 ·
Blackhawk-17 avatar image
Blackhawk-17 answered
How old is your last FULL backup? If you have a really old base FULL you could experience this sort of situation. You don't just do differentials forever after an initial backup. You need new FULL backups to begin a new round of differentials. For instance some places do a FULL on Saturdays and daily differentials on all the other days. When I first read the question my reaction was "victim of their success", by which I mean you must have a busy dB. Then after having some coffee I realized you were talking 2/3 of the dB as a differential. That's large, (not unheard of though) but 22 GB is a lot of activity.
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ramesh 1 avatar image ramesh 1 commented ·
you how old is full backup up, we have full backup on every saturday
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.