principlesqlMinor
SQL Backups - Full vs Differential vs Log Shipping
Viewed 0 times
fullbackupslogsqldifferentialshipping
Problem
I'm looking to refine our SQL backups utilizing Full, Differential, and Log Shipping. Currently, I am only performing full backups once at night. This process is taking so long that it is running throughout most of the work day. As we would like to use our standby server for reporting, this is not ideal.
I would like to increase the speed of our backups, but at the same time make things a bit more reliable. What would be the best way to accomplish this with the above 3 methods? Full backups once weekly, differential backups once daily, and log shipping every 15-30 min?
What considerations should I be making here? (Sorry this is a bit vague, just trying to get some input)
I am using SQL Server 2005
I would like to increase the speed of our backups, but at the same time make things a bit more reliable. What would be the best way to accomplish this with the above 3 methods? Full backups once weekly, differential backups once daily, and log shipping every 15-30 min?
What considerations should I be making here? (Sorry this is a bit vague, just trying to get some input)
I am using SQL Server 2005
Solution
By "log shipping" I'm guessing you're meaning log backup? Log shipping is a high availability strategy.
If your full backups are taking the better part of a day, yes I recommend that you switch things up a bit. But that all depends on a lot of environment specific parameters. For instance, do you have a big window on the weekend where you will be able to do a full backup without interfering with users? (If not, you may need to look into filegroup backups)
A full backup once a week, and diff backups daily (again, in a maintenance window) is pretty common. As for your t-log backups, you need to determine the maximum amount of allowable data loss. In other words, if you back up your transaction logs every 30 minutes, you will have a maximum of 30 minutes data loss if the tail of the log is corrupt and unrecoverable. So determination will need to be made for transaction log backups.
(P.S. If you actually did mean Log Shipping then please clarify and I'll rework this answer. But by judging by your question I am assuming you just mean log backups)
If your full backups are taking the better part of a day, yes I recommend that you switch things up a bit. But that all depends on a lot of environment specific parameters. For instance, do you have a big window on the weekend where you will be able to do a full backup without interfering with users? (If not, you may need to look into filegroup backups)
A full backup once a week, and diff backups daily (again, in a maintenance window) is pretty common. As for your t-log backups, you need to determine the maximum amount of allowable data loss. In other words, if you back up your transaction logs every 30 minutes, you will have a maximum of 30 minutes data loss if the tail of the log is corrupt and unrecoverable. So determination will need to be made for transaction log backups.
(P.S. If you actually did mean Log Shipping then please clarify and I'll rework this answer. But by judging by your question I am assuming you just mean log backups)
Context
StackExchange Database Administrators Q#17295, answer score: 5
Revisions (0)
No revisions yet.