Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-08-08 : 12:00:37
|
| Mitch writes "We have a relatively large database, 176 GBytes, and our full backups are starting to intrude into other maintenance jobs during the late evening, early morning hours. So, we decided to look into using "differential" backups.I started using differentials last week and let them run instead of the full backup for a week. The first one was 44 Gig; the second was 66 Gig. I fully expected to have all subsquent differentials to be multiples of the first -- 44, 88, 132, etc.To my surprise, the differential's file size leveled out at about 67 Gig for the remaining 3 nights of the week. Any idea why this might be and if whether or not I'm doing something wrong?In addition, we restore to a different database left in Stand-by mode (we usually do the restore the fulls, but it was the differentials last week) so that we can recover very quickly if necessary and we have the ability to query yesterdays data very easily. I've started comparing this Stand-by server to our production server and the data seems to be consistent. We'd like to modify our backup strategy based on this new information, but, of course, I'm a little concerned with the integrity of the differential data since the file size doesn't continue to grow as expected.Mitch" |
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-08-08 : 13:10:05
|
quote:
Originally posted by AskSQLTeam
Mitch writes "We have a relatively large database, 176 GBytes, and our full backups are starting to intrude into other maintenance jobs during the late evening, early morning hours. So, we decided to look into using "differential" backups.I started using differentials last week and let them run instead of the full backup for a week. The first one was 44 Gig; the second was 66 Gig. I fully expected to have all subsquent differentials to be multiples of the first -- 44, 88, 132, etc.To my surprise, the differential's file size leveled out at about 67 Gig for the remaining 3 nights of the week. Any idea why this might be and if whether or not I'm doing something wrong?In addition, we restore to a different database left in Stand-by mode (we usually do the restore the fulls, but it was the differentials last week) so that we can recover very quickly if necessary and we have the ability to query yesterdays data very easily. I've started comparing this Stand-by server to our production server and the data seems to be consistent. We'd like to modify our backup strategy based on this new information, but, of course, I'm a little concerned with the integrity of the differential data since the file size doesn't continue to grow as expected.Mitch"
A differential backup only contains the pages that were changed since the last full or differential backup - so what you're seeing is perfectly reasonable.Paul RandalDev Lead, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-08-08 : 14:10:20
|
quote:
Originally posted by paulrandalA differential backup only contains the pages that were changed since the last full or differential backup - so what you're seeing is perfectly reasonable.
My experience has been that Differerntial backups contain the changes since the last full, not the last diff. I have never applied more than one DIFF on a restore. I'll have to test that out.EDIT:I think another possible explanation for the diff backup size is that the same blocks in the database are being changed. Is that possible in this example?-ec |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-08-08 : 14:28:45
|
quote:
Originally posted by eyechart
quote:
Originally posted by paulrandalA differential backup only contains the pages that were changed since the last full or differential backup - so what you're seeing is perfectly reasonable.
My experience has been that Differerntial backups contain the changes since the last full, not the last diff. I have never applied more than one DIFF on a restore. I'll have to test that out.-ec
Sorry - I'm getting confused with what we had in SQL 2000 and what we have in SQL 2005.In SQL 2000, the only option is full differential backups - so Mitch, this implies that there's no activity on your database after the diff backup size reaches 67Gb - correct?In SQL 2005, we've introduced partial differential backups, which only backup what has changed since the last full backup or partial differential backup.ThanksPaul RandalDev Lead, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
|
|
|
|
|
Differential Backup file size