3

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

Improve this question

1 Answer 1

Reset to default
5

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)

Improve this answer
6
  • Thanks, Shark. I should clarify, I am doing a backup/restore to have a "warm" standby server. The restore process is what appears to be taking the most time. 1) Will differential backups improve the speed of the restore process? 2) Will t-log backups affect the performance of my main server?
    – unforgiven1987
    May 1, 2012 at 13:08
  • Oh & yes, I did mean Log Shipping.
    – unforgiven1987
    May 1, 2012 at 13:36
  • @unforgiven1987 Ah, ok. Well even with log shipping you will have a max data loss of whatever interval you set up to schedule the job. If you're looking for a warm standby of the database, then log shipping will get you that and you won't need to constantly do full/diff backups/restores. Do full/diff backups for basic DR, not necessarily for log shipping itself. Those will be two separate entities. As for t-log backups, their impact should be negligable and acceptable in order to either 1) use log shipping and/or 2) ensure minimum data loss.
    – Thomas Stringer
    May 1, 2012 at 13:54
  • With Log Shipping, is it customary to do a single full backup & restore, then continue on with log shipping indefinitely (until something happens and the LSN chain is broken)? Or is it better to do a full backup at a specific interval (say once a week), then use Log Shipping for the in between times to minimize restore times?
    – unforgiven1987
    May 1, 2012 at 13:58
  • 1
    @unforgiven1987 The former. Consider it an ongoing high availability strategy.
    – Thomas Stringer
    May 1, 2012 at 14:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.