November 14, 2008 at 10:24 am
I need some advice. I'll lay out my current backup plan (that I inherited) then what I'm wanting to move to.
Currently:
*Full Backups Daily at 6AM: This lets our overnight processes finish up prior to running the full backup and lets our Differentials remain as small as possible
*Differential Backups every 2 hours.
Desired:
*Full Backup daily at 6AM.
*Transaction log backups periodically throughout the day.
The reason for the move to transaction log backups should be obvious - the ability to perform point-in-time restores. And the added benefit of being able to go through transaction logs to see what's been happening due to the change to Full recovery model.
I guess I only have one question:
Would it be beneficial to continue to run the differential backups and use those in a restore routine to minimize the number of transaction log backups that I would have to process in order to do a restore? Currently, I can get data restored with two-hour granularity. If I was to keep the differentials in place and add t-log backups my restore strategy would look something like:
a) Restore full backup with NORECOVERY
b) Restore most recent differential prior to point-in-time desired with NORECOVERY
c) Restore t-logs from differential to desired point-in-time using WITH RECOVERY on last one.
Or should I just go with a daily full backup and use the t-log backups to get to my desired restore point-in-time?
Consider that we use our backups regularly to restore to a development box in order to use older data for testing purposes. So if there is a time benefit to using the differentials in conjunction with t-log I would probably go that route. I'm hoping that someone has 1st-hand knowledge on this so I don't have to write out both scenarios in order to determine the best plan.
Thanks in advance,
Kent
November 14, 2008 at 10:36 am
kent.kester (11/14/2008)
I need some advice. I'll lay out my current backup plan (that I inherited) then what I'm wanting to move to.Currently:
*Full Backups Daily at 6AM: This lets our overnight processes finish up prior to running the full backup and lets our Differentials remain as small as possible
*Differential Backups every 2 hours.
Desired:
*Full Backup daily at 6AM.
*Transaction log backups periodically throughout the day.
The reason for the move to transaction log backups should be obvious - the ability to perform point-in-time restores. And the added benefit of being able to go through transaction logs to see what's been happening due to the change to Full recovery model.
I guess I only have one question:
Would it be beneficial to continue to run the differential backups and use those in a restore routine to minimize the number of transaction log backups that I would have to process in order to do a restore? Currently, I can get data restored with two-hour granularity. If I was to keep the differentials in place and add t-log backups my restore strategy would look something like:
a) Restore full backup with NORECOVERY
b) Restore most recent differential prior to point-in-time desired with NORECOVERY
c) Restore t-logs from differential to desired point-in-time using WITH RECOVERY on last one.
Or should I just go with a daily full backup and use the t-log backups to get to my desired restore point-in-time?
Consider that we use our backups regularly to restore to a development box in order to use older data for testing purposes. So if there is a time benefit to using the differentials in conjunction with t-log I would probably go that route. I'm hoping that someone has 1st-hand knowledge on this so I don't have to write out both scenarios in order to determine the best plan.
Thanks in advance,
Kent
It partly depends on how often you will be running the transaction log backups between full backups. If you are doing those every 15 minutes, you'd probably want to keep some of the differential backups (maybe not every 2 hours, but maybe so) so that if you did have to restore the database, you would not have to use the full backup plus 20 or more transaction log backups to restore the database.
November 14, 2008 at 10:51 am
Is this a matter of making it easier for me to perform the restore or a matter of how long it takes for the server to perform the restore steps? I intend to write a restore script (actually, rewrite my current one) that will make this a matter of running an easy stored procedure. If keeping the Diffs doesn't help the database restore faster I'm not inclined to do them. If they will help make the restore go faster (knowing that there is no human intervention in the process) then I will definitely keep them in place.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply