differential backups larger than full backup
-
We are doing a full backup every Sunday morning, differentials every morning except Sunday and then Transaction log backups every 4 hours between 6am and midnight.
The full backup on Sunday was about 7 gig. The differential backup yesterday was about 8 gig. I manually did a full backup yesterday around 9am and the differential backup today is 9.5 gig.
I thought the differential backup was all the changes since the last full backup. What full backup is my differential looking at? Here is the syntax of the differential job. Do I need to change the settings?BACKUP
DATABASE [TMIS_LIVE] TO [tmis_differential] WITH NOINIT , NOUNLOAD , DIFFERENTIAL , NAME = N'TMIS_LIVE differential backup', NOSKIP , STATS = 10, NOFORMAT DECLARE @i INTselect @i = position from msdb..backupset where database_name='TMIS_LIVE'and type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='TMIS_LIVE')RESTORE VERIFYONLY FROM [tmis_differential] WITH FILE = @i
-
Not too sure from your sql command, but I would guess you are appending your differential backups to the same file. This will cause your differential backup file to grow larger. I would do your backup to seperate backup files each day and see how that works.
hth
-
I notice you're appending your backup to the backup device (WITH NOINIT). Have you checked that the 9.5GB doesn't consist of more than one backup?
John
-
NOINIT will be causing your backups to append - that's why the backup is growing - it should be 1.5GB accordig to your figures
MVDBA
-
Okay, I'm a little confused. I thought we wanted the differential backups to append. I thought that meant, take the differential from Monday and add to Tuesday. Wednesday and so on. THen I would have all the changes since the full back on Sunday. Then the next week on Monday (after the full backup on Sunday) it would be smaller? Is that how it works?
-
The differential backup records all changes to the database since the last full backup. So if you wanted to restore on Friday, you'd need Sunday's full backup and Friday's differential backup (assuming it has already been taken). Transaction log backups work differently: if you want to restore from those, you need the whole chain of them from the previous full backup.
Hope that helps
John
-
Thanks, I understand how the fullbackup, differential, and transaction log backups work. My question is with the INIT and the appending. I assumed that the differential is the changes from the last full backup. Meaning a full is taken on Sunday and then the differential from Friday is ALL the changes made since Sunday full backup. Meaning...it appends all the changes. I thought that's what NOINIT means....to append the changes (from Monday, Tues, Wed, etc).
Wait, I think I just answered my won question.
NOINIT is appending ALL the changes, even after the full backup. What was happening in my scenario is that even after the next Sundays full backup is taken, the differential would not start all over, it would just keep adding? Is that interpretation correct?Also, if you are using INIT, do you still need SKIP? Or can I keep NOSKIP
-
No, that's not right. All NOINIT does is tells SQL Server whether to overwrite what's already in the backup file. So if you'd backed up with INIT, then Tuesday's differential would have overwritten Monday's, but Tuesday's differential would still back up all changes since the full backup.
Can't remember what SKIP does off the top of my head, I'm afraid. It should tell you in the BACKUP DATABASE topic in Books Online.
John
-
as long as your full backup is INIT and your differentials are NOINIT you're on course for having the right backups
(assuming they both go to the same file)
then we would expect to see the file climb in size over the differential period and then shrink at the next full backup
although i would suggest that i'm not personally keen on storing differentials in the same file as your main backup. - if any part of the file becomes damaged you will lose your full backup. i tend ot keep them all seperate so that if any of my differential files are damage (god forbid) then i can restore from full backup + remaining differentials up to the damaged one and then do a t-log restore for the rest.
MVDBA
-
Thank you everyone. This has really helped a great deal. 🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply