question

ramesh 1 avatar image
ramesh 1 asked

Problem with my differential backup

hi team, in my server we will havefull backup on saturday 23.00 and daily differential backup every day at 3.00 am. but these i can find that differential back size is equl to my full backup. what might be the problem help me
sql-server-2005differential-backup
5 comments
10 |1200 characters needed characters left characters exceeded

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

Ramesh, is the first differential backup (Sunday 3am) as big as the full backup?
0 Likes 0 ·
I can't retag this to "sql-server-2005 differential-backup". Can anyone else?
0 Likes 0 ·
@Thomas.. done.
0 Likes 0 ·
@Ramesh All your Differential backups are of same size?
0 Likes 0 ·
@Ramesh - did you set this up as a Maintenance task via the wizard or through scripts?
0 Likes 0 ·
Leo avatar image
Leo answered
Not sure where you setup wrong. I just test on my machine with Diff Backup and size are quite smaller then Full Backup. You can try to run Diff Back Up manually in SSMS and check the size of the backup, you might need to go through your schedule backup again if your manual backup size is smaller then schedule backup.
10 |1200 characters needed characters left characters exceeded

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

sp_lock avatar image
sp_lock answered
Ramesh Do you do any maint tasks? Index rebuild could be the cause of this.
10 |1200 characters needed characters left characters exceeded

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 answered
what it mean :(
1 comment
10 |1200 characters needed characters left characters exceeded

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

Ramesh, can you add a comment to the answer you need futher details on?
0 Likes 0 ·
prakskarry avatar image
prakskarry answered
Differential backup contains all files that have changed since the last FULL backup . These are not incremental.If you are seeing differential backup size eqal to full backup then there must be something wrong. Post output of RESTORE HEADERONLY FROM for each backups.
10 |1200 characters needed characters left characters exceeded

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

David Wimbush avatar image
David Wimbush answered
A differential backup is a backup of all the data pages that have changed since the last full backup. (Well, technically the extents that have changed.) If all the pages have changed, it will be as big as the full backup. Using your backup pattern, I would expect the differentials to start out fairly small and get bigger as the week goes on. But it depends on what happens to the database. As others are suggesting, if you do something like rebuild all the indexes, the differential backup will be as big as the full backup.
10 |1200 characters needed characters left characters exceeded

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

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.