Importance of where you store your backups

A couple of weeks ago I kicked off a survey about where you store your backups.

Here are the results:

The’Other’ responses are:

  • 8 x ‘Stored on same SAN, different LUN as databases, with off-site copies.’
  • 6 x ‘Copy 1 stored on same SAN (1-2 days) for quick restore in non-physical disaster. Copy 2 stored on virtual tape device for longer term (30 day) storage. Copy 3 sent off-site on tape.’
  • 5 x ‘Stored on local HDD array then OS backups nightly to off-site location.’
  • 4 x ‘Stored on local SAN, replicated to remote SAN, stored to tape and then off-site.’
  • 4 x ‘Stored on SAN and then backed up to off-site storage.’
  • 4 x ‘Stored on SAN on same site, replicated to remote SAN. Also backed up from local SAN to tape.’
  • 2 x ‘Amazon S3.’
  • 1 x ‘Backed up to local Data Domain device then replicated to another Data Domain device in a remote site (no tape).’
  • 1 x ‘Certain datacenters have snapshots on Production Volumes and off-site disk backup for 3 weeks. All other centers are on-site and off-site TAPE.’
  • 1 x ‘Database dumps stored on local HDD. Dump area then backed up to tape. On weekly basis tapes are then stored in fire safe for next couple of weeks.’
  • 1 x ‘Data Domain and tape.’
  • 1 x ‘External USB hard drive.’
  • 1 x ‘Managed backup service from hosting provider.’
  • 1 x ‘Mirrored to 2 different SANs.’
  • 1 x ‘Snapshot on local SAN, replicated to remote SAN.’
  • 1 x ‘Stored locally.’
  • 1 x ‘Stored on backup database hardware via share to production server using private network connection. Backed Up to cloud nightly.’
  • 1 x ‘Stored on different SAN as databases, SAN replication to offsite SAN for DR.’
  • 1 x ‘Stored on local HDD of backup server, then tape, then off-site.’
  • 1 x ‘Stored on NAS, then on tape, then offsite.’
  • 1 x ‘Stored on SAN + replicated but also on tape and off-site.’
  • 1 x ‘Stored on Seperate SAN, mirrored to DR with monthly offsite tape backups.’

Summary

The results are very interesting, with most people storing backups locally and off-site, which is what I recommend and what I expected the results to show.

However, there’s really no right answer except that you have to have the backups to allow you to recover data within your downtime and data-loss SLAs. This doesn’t mean you have to have off-site backups, if your SLAs allow complete data loss and recreating the data from scratch.

Food for thought:

  • For those of you storing backups only on the same SAN as the databases, what’s your DR strategy if the entire SAN is corrupted or destroyed?
  • For those of you storing backups only in the local data center, what’s your DR strategy if the data center is destroyed? (It happens – fire, flood, tornado, bomb, …)
  • For those of you with backups stored off-site, how quickly can you bring the backups on-site if necessary? And what if the only data center you have is destroyed? What good are the off-site backups then?

If you can’t recreate the data completely from scratch, you must have off-site copies of your backups.

It’s not just about having good backups, and even having access to them when things go wrong, i’s also making sure that you can restore them and get back up and running within your downtime and data loss SLAs, no matter where you get the backups from.

When did you last check that’s possible for your environment?

Probably the worst DBA sin is losing a database and its backups too. IMHO there’s no excuse for that – don’t let it happen to you!

Survey: how often do you perform a full backup?

In this survey, I'm interested in how often you perform a full database backup of your production databases. I'll editorialize the results in a week or two.



Thanks!

SQLskills holiday gift to you: all 2011 Insider videos on YouTube

As we approach the 2012 holiday season, we want to give the SQL Server community a holiday gift to say ‘thank you’ for all your support during 2012, and what better gift than more free content?!

As many of you know, I publish a bi-weekly newsletter than contains an editorial on a SQL Server topic, a demo video, and a book review of my most recently completed book. We’ve decided to upload all the 2011 demo videos onto YouTube so everyone can watch them – nineteen videos in all totally around 3 hours of content.

Here are the details:

If you want to see the 2012 videos before next December, get all the newsletter back-issues, and follow along as the newsletters come out, just sign-up at http://www.SQLskills.com/Insider. No strings attached, no marketing or advertizing, just free content.

Happy Holidays and enjoy the videos!