A little bit more information on multi location backups

On of the advantages of backing up to multiple locations is that the data is read on the SQL Server just once, this is an advantage because the IO on the database files is kept to a minimum (in comparison on doing two backups one after the other). Then the data is sent to all destenations in parallel this results in the fact that the backup will take as long as is takes to write to the slowest target location.

You can backup full, differential and log backups to multtiple locations the MIRROR TO option is build in into all of them

I do not backup full backups to two locations (you can alsways take the backup before the last to start your restore if you lose the last one) but I backup my logs to two locations to reduce the risk of a broken log chain by a missing or corrupt file.

If you are really paranoid you should write the data trough two different network cards from different manufactuers with different drivers to avoid data corruption by a faulty driver and then have a constant verify by restore on all destinations.

  • 5 stars
    Comment from: cmu
    2011-01-28 @ 10:02:38

    Good idea! Additionaly you can set up log shipping to proof the consistency of your log backups.

  • Comment from: =tg=
    2011-01-28 @ 10:59:41

    Log shipping is a great way to do unfortunately with the build in log shipping you cant use multiple locations, but you can write your own log shipping to do so

Leave a comment

You must be logged in to leave a comment. Log in now!

If you have no account yet, you can register now...
(It only takes a few seconds!)