June 21, 2002 at 9:14 am
My back up strategy is as follows for a SQL server 2000 machine.
1) Recovery model = SIMPLE
2) Weekly full backup.
3) Daily differential backup ( we do not back up the log as the transactions can be replayed for the day)
The database increases in size by about 1 gig a day. So the diffrential backup should not exceed 7 gig for the week.
However i see that the differential backup is about 33 Gig. A full backup of the db is about 70 gig.Various maintainance jobs doing dbreindex run during the week. Why is the differential backup so large ?
thanks in advance for you help
-srini
June 21, 2002 at 9:47 am
Do you have the backup set to INIT? If not then you are appending to that backup file and thereby your file size would continue to grow.
Hope this helps.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
June 21, 2002 at 12:12 pm
yes the backup command has init on it
June 21, 2002 at 3:13 pm
I think the documentation in BOL is kind of misleading because it says that only the information that has changed since the last full backup is included in a differential backup however, when I run a full backup on a test database resulting in a 1 Gig file, and immediately follow with a differential backup on the same database I get a 250 MB differential file.
Obviously still a 75% reduction in space but not what you might expect from reading BOL.
Don't know if that helps but what you are seeing may be accurate. Sorry I couldn't help more.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply