In the past, when I’ve set up log shipping, it was primarily as a DR device, designed to get copies of logs to another server ASAP. The idea being that if we had an issue on the production server, we could switch to the secondary server quickly and lose minimal working time for the company.

To that end me, and most everyone I knew, restored those logs ASAP. If you ran log backups every minute, you didn’t want more than a couple of minutes before you could bring the secondary online and start moving clients over.

That’s a good process, though to a large extent it has been superseded by database mirroring for quick recovery on another server. Mirroring also includes the ability to automatically direct clients to the mirror server, which isn’t something log shipping can do.

However you don’t always want to restore those logs immediately. Not all disasters require you, or even force you, to switch servers. Consider those “accidental” disasters. What do you do if a user deletes all customers, or the DBA accidently sets all orders to the same quantity because they forgot a WHERE clause?

If you restore immediately, those changes are likely applied on the secondary server before you can even contact the DBA, or make a remote connection to the secondary server and shut off the log shipping process.

If, however, you have, say a 3rd database, one that delays restoring logs for an hour or two, you’ll still have that original data on that server. Even if you’ve made some changes in the last few hours, you could “catch up” all the logs except for the one where you made the mistake, and then move that data over with an SSIS Wizard package.

That might save you a lot of time, or explanations, when you do make a mistake. And rest assured, sooner or later you will.

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Comments

Posted by Chris Hamam on 21 April 2010

Hi Steve

I agree with the principles of SQL Server Log Shipping:

--take log backups as frequently as possible

--copy all backups off site as soon as possible (ie: immediately)

However, I prefer and generally recommend not to use this Log Shipping mechanism, as simply writing your own backup & restore jobs provides far more control, and does not require a monitoring server.

Then it is possible to use these in a "pre-production" environment that we generally refresh daily or more frequently, which verifies the backups are working, and provides this Production database copy as-at any point in time we require.

In a critical situation, like the Production data changes you mentioned, this is far easier and quicker to do a restore, than hoping for the right delay interval.

--Chris

Posted by Steve Jones on 23 April 2010

You don't need a monitoring server to implement this. And what if you have a 100GB database, or even 20GB and someone whacks one table? The restore isn't necessarily the best way to fix things quickly.

Putting up a log shipping, letting it run to another server with a 2 hour delay is easy. No monitoring server, no issues. It's always 2hours behind in case you lose some data.