Log Shipping Without Maintenance Plans


September 29, 2011 by Mike Hillwig

I have this crazy idea spinning around in my head–setting up log shipping without using a maintenance plan. It’s either stupid, crazy, or brilliant. It’s probably a combination of the three.

I’m working with a company that hosts a database application for clients. In this environment, we’re growing quickly. I need a DR solution that will scale incredibly well. To support scalability, I’ve banished maintenance plans. They’re great, but they don’t exactly scale well.

So I’m thinking of writing the log shipping into our transaction log backup process. Clearly, I need to test it, but I’m thinking it has the potential to work if written properly.

The concept is that I know the filename based on the transaction log backup I’ve just done, and by passing that into an xcopy command line, I can copy the log file to the DR server. The DR server will have a similar process running that will look for files that have been copied, get the correct data from the source and restore the log file.

Will this work? Maybe. But at this point, I’ve thought about it enough to know that I’ve got to try.

  • My favorite way to do this is to simply do all transaction log backups to a UNC path (\\servername\sharename) and then on the restoring servers, restore every t-log file, every time. It’s brute force, but it works – SQL Server won’t let you restore a t-log file that can’t be applied.

    There’s a couple of drawbacks. You have to make sure the directory list is retrieved in date/time stamp order so that t-logs are restored in order. You also have to monitor to make sure files are getting successfully restored, because you’ll get lots of errors each time – you just want to know that restores are still happening.

  • I wrote up about writing your own Log Shipping years ago. You can read up about it http://www.tek-tips.com/faqs.cfm?fid=5754.