Backup Solution

Question

I have 2 ideas for my backup solution and was looking to get feedback from the community about this. I'm running SQL Server 2008 Enterpise.

1) Once a week full back up daily incremental back ups locally. Then setting up a scheduler to copy the files to my backup server.

2) Use log shipping. To be honest, I haven't done this before but it looks relatively straight forward to implement. I am not sure of the overheads involved though. This could also be a problem if the network goes down.

I'm actually for the log shipping solution even if I have no prior experience with it. Whadya guys think? Replication is out cos I don't want any schema changes. Just looking for a fuss free easy set up.

Thanks!

Answer 1

Log shipping is great for keeping a spare server up-to-date (there are a number of reasons why you might be interested in this) but it does not constitute a backup as such.

Likewise having your database server and backup server RAIDed provides extra resilience, which might also be very useful.

But personally, I wouldn't forego standard backups. The additional benefit of these is that you will have a series of snapshots available to you at any given time. For example, I maintain a copy of one business system for use with month- and year-end reporting at a HQ site.

Ideally, you should use a combination of measures (appropriate to your circumstances) to provide more comprehensive cover. Since having a standby server is less of issue in most of my circumstances, I don't bother with log-shipping, but that won't necessarily apply to you. But don't dismiss conventional backups either.

I'd also recommend that you do your backups straight to your backup server. It seems a little wasteful to backup locally and then copy... I'm assuming that your backup server is suitably robust.

Answer 2

In my opinion:
Backups are for backups / Disaster recovery
Log Shipping is for warm standby / Business Continuity

it depends on what business need you are trying to satisfy ...