Hi,
Using SQL Server 2005, we have a 2.8Gb database under the Simple recovery model. The database contains ~50M rows and each night ~60k rows are loaded(appended) to the database by a SSIS task.
We configured a Maintenance Plan which is executed once a week to perform a full backup of the database. The resulting backup file is ~2.8Gb, as expected.
We also configured another Maintenance Plan which is executed every day, a few hours after the SSIS task is executed, to perform a differential backup. To our surprise, the resulting backup file is about the same size as the full backup, ~2.8Gb when it should only be a few MB (only 60k rows are added to the database)
When we launch the "Restore Database" wizzard we clearly see the different backup set, Full and Differential but they all have about the same size (same for the physical backup file on disk).
Is there anything we are missing, why are the differential backup that big?
Thanks for any advice.
Hi Math,When u r setting Differential backup have u checked for options like
Append to the existing backup set
Overwrite all existing backup sets
when we use append to the existing backup set it appends to the existing differential backup set, so the differential backup obviously increases & even nearer to the full backup size.
Regards
Karna|||
Can you post the exact syntax that you're using?
That would help to narrow it down.
Thanks,
|||Hi,After getting into SQL Server 2005 Management Studio, in the Backup task of certain database when u click on options u'll find option buttons such as
Append to the existing backup set
Overwrite all existing backup sets
From these above options u can choose any one of them, by default Append to the existing backup set is enabled.
I hope this would helpful.
Regards
Karna
No comments:
Post a Comment