Recently I was reading up on log shipping and noticed this paragraph in the Books Online entry. It is in the part about the restore jobs used for log shipping and says
"In contrast, delaying restore jobs, perhaps by several hours, can be useful in the event of a serious user error, such as a dropped table or inappropriately deleted table row."
That's a good benefit of log shipping, and one that I'm not sure many people think of when configuring log shipping. Instead the primary purpose is disaster recovery, and that term connotates the complete loss of the primary server, necessitating the movement of all clients (and hence all data) to the secondary.
But that's not what most disasters consist of. Most of the disasters I've encountered in my career were referred to as "errors", often accompanied with an audible "whoops" as someone stares at a query they just ran that's missing a WHERE clause. Or they were disasters when the phone rings and some random employee working with an application asks "how often" we back up xxx application.
In the good old days of v6.5, we could restore a single table and usually fix these errors. With all the wonderful modern versions of SQL Server, we usually perform a full restore. That's if we don't have some nice third party tool like SQL Backup or Litespeed. (Hint, those products are worth the $$)
However maybe there's something else you should be considering. For critical databases, perhaps having a database log shipped to some other server, with a couple hour delay on the restores, would allow you to recover from many of these disasters. It wouldn't even have to be a server, or cost much, with today's powerful desktop CPUs and $100 TB drives. You could set up one machine, install SQL Server and use that to receive log shipped databases from a few of your important databases.
I've used log shipping for years in a variety of situations, and it's worked well for me. Maybe it's a tool you ought to consider adding to your DBA kit as well.
Steve Jones
The Voice of the DBA Podcasts
The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there.
You can also follow Steve Jones on Twitter:
or now on iTunes!
- Windows Media Podcast - MB WMV
- iPod Video Podcast - 17.4MB MP4
- MP3 Audio Podcast - 4.0MB MP3
Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.
