July 26, 2011 at 4:25 am
We have a VLDB (simple recovery model), and our backup strategy is, to create a full backup every sunday, and a diff backup alle other days.
Our full backup takes up to 10 hours, and a diff maybe 15 minutes.
Sometimes the full backup fails (or maybe even killed, if it runs too slow, and cross a maintenance window).
After that, our differential backups takes all the data in the database, instead of only the changes, so it takes 10 hours every day, untim the next full backup is succeeded.
Is this normal ?
July 26, 2011 at 4:37 am
Our full backup takes up to 10 hours, and a diff maybe 15 minutes.
it seems like there is not much modification in your database and thats why your diff finish so quickly.
After that, our differential backups takes all the data in the database, instead of only the changes, so it takes 10 hours every day, untim the next full backup is succeeded.
Is this normal ?
are you mentioning here that after failure of full backup, your diff backup takes 10 hrs. if yes then it should not be the case.
I will prefer to configure log backup every 15 min or 1 hour instead of differential backup.
----------
Ashish
July 26, 2011 at 5:09 am
crazy4sql (7/26/2011)
Our full backup takes up to 10 hours, and a diff maybe 15 minutes.
it seems like there is not much modification in your database and thats why your diff finish so quickly.
Yes, it is a BI Setup with 5 years of historical data.
After that, our differential backups takes all the data in the database, instead of only the changes, so it takes 10 hours every day, untim the next full backup is succeeded.
Is this normal ?
are you mentioning here that after failure of full backup, your diff backup takes 10 hrs. if yes then it should not be the case.
Thank You, I did not think so either. I will investiage it further with our external partner.
I will prefer to configure log backup every 15 min or 1 hour instead of differential backup.
The Recovery model is Simple, so there a no log backups.
July 26, 2011 at 5:14 am
The Recovery model is Simple, so there a no log backups.
thanks, I should read it in your earlier mail. Why cant you switch it to full recovery?
Also as you mentioned its historical data, you no need to run the full backup every day. Instead run full backup every week/fortnight/month and then run the differential backup every day/alternateday/week.
This calculation you can do based on how frequently your data is being updated.
----------
Ashish
July 26, 2011 at 5:37 am
crazy4sql (7/26/2011)
The Recovery model is Simple, so there a no log backups.
thanks, I should read it in your earlier mail. Why cant you switch it to full recovery?
It might be possible, but there are no reason to switch, as we easily can recreate data between a diff backup and crash.
Also as you mentioned its historical data, you no need to run the full backup every day. Instead run full backup every week/fortnight/month and then run the differential backup every day/alternateday/week.
This calculation you can do based on how frequently your data is being updated.
We update a lot of data every day, but the backup is usually running fast enough...
We will have to find out, why the full backup sometimes is taking too long or fails, and why the diff backups sometimes take as long as a full backup, when the full backup fails.
July 26, 2011 at 5:45 am
Or one more alternate is to archive old data to new filgroup and then schedule filegroup backup which will be much quicker and less space consuming.
----------
Ashish
July 26, 2011 at 6:02 am
crazy4sql (7/26/2011)
Or one more alternate is to archive old data to new filgroup and then schedule filegroup backup which will be much quicker and less space consuming.
Yes, and we actually have split every years partitions in separate filegroups, but unfortunately, we can't make them read-only, as we still can get new data for earlier years partitions. An alternative is to have change partitions for earlier years, but that would require some rework in our ETL applications, and the cost/benefit for that haven't been beneficial yet...
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply