Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

The Log Shipping Standard

By Steve Jones,

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

Everyday Jones

The podcast feeds are available at Comments are definitely appreciated and wanted, and you can get feeds from there.

You can also follow Steve Jones on Twitter:

Overall RSS Feed: or now on iTunes!

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

Total article views: 620 | Views in the last 30 days: 1
Related Articles

Day 2 of 31 Days of Disaster Recovery: Protection From Restoring a Backup of a Contained Database

Day 2 of 31 Days of Disaster Recovery: Protection From Restoring a Backup of a Contained Database ...


Day 3 of 31 Days of Disaster Recovery: Determining Files to Restore Database

Day 3 of 31 Days of Disaster Recovery: Determining Files to Restore Database 31 Days of Disaster ...


Day 29 of 31 Days of Disaster Recovery: Using Database Snapshots to Restore Replicated Databases in Test

For day 29 of my 31 Days of Disaster Recover series, I want to talk about restoring replicated datab...


SQL Server Disaster Recovery Planning

While administrating SQL Server database, one of the important part is to prepare a plan to work aga...


Day 27 of 31 Days of Disaster Recovery: Restoring Part of a Database

Today is day 27 of my series 31 Days of Disaster Recovery, and I want to talk about restoring a...


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones