How do we setup a remote Standby / Read-Only database to accept Log Shipping?

Question

We have two SQL 2008 servers on different networks that will soon have a VPN connecting them. Soon is 2-3 weeks. If that were in place now we could easily setup Transaction Log Shipping (or other method) of keeping the destination (Standby / Read-Only) database up to date. How do we setup the LSRestore_ job on that destination database while in Read-Only mode? We will copy the *.trn files manually at first. My supervisor says he saw a post saying this is possible. We were just trying to avoid doing a full database backup and copying that every time. Thanks in advance for any guidance on this.

Answer 1

As the log shipping destination gets it's settings from the primary server, you cannot use the standard log shipping job in an "offline mode" - it would need a VPN.

So here's how I'd go about it (It's pretty much how log-shipping does it anyway):

• First, you need to manually restore a full backup (WITH NORECOVERY)
  • NORECOVERY allows transaction logs to be restored afterwards - the database is not accessible at this point.
• Using exec xp_cmdshell 'dir /b *.trn' you can get a list of .trn files into a temporary table
• Using a cursor, iterate through the temporary table, calling a RESTORE LOG command for each .trn file
  • Use RESTORE WITH STANDBY, so the database is left read-only after the restore
• After each .trn file is successfully restored, move it to an archive folder or delete it.
  • use xp_cmdshell to do this.