5

We have a few huge databases we would like to log ship on SQL Server 2008. Unfortunately the link between primary and secondary servers is fast enough to handle log shipping but the initial backup/copy would tie up bandwidth for days, if not weeks, which we would like to avoid if at all possible.

We have made a copy (now a few days old) by backing up the primary database to a hard disk and physically taking the hard disk to the secondary machine and restoring the database there.

Primary and Secondary Servers are in different geographical locations. Primary Server is on SQL Server 2008, Secondary on 2008 R2 (Don't ask why!)

There seems to be no way we can find to get log shipping (or mirroring) to work without starting the backup/copy/restore process from scratch.

Any ideas/solutions would be greatly appreciated.

SeanDav
  • 161
  • 1
  • 3
  • The very first time you bring the database online on R2, it will be upgraded to version 661 and stop accepting updates from the 2008 log backups (version 655). You need them to be at the same version if you want to use the database on the secondary in any way. – Remus Rusanu Oct 27 '10 at 17:55

4 Answers4

5
  1. Turn on log shipping, but don't add any targets
  2. Take a full backup of the primary database
  3. Move the database backup to the secondary server
  4. Restore backup WITH NORECOVERY
  5. Enable the secondary server as a log shipping target
  6. SQL Server will then copy all the log shipping files that it's generated to date to the new server over the link and restore them

I've done this dozens of times and it's never failed, so if it doesn't work then you might want to update the question with the EXACT steps you've taken.

Mark Henderson
  • 68,316
  • 31
  • 175
  • 255
4

If your database is in full recovery mode then you just need to do the following:

  • Take full backup
  • move drive to standby
  • restore database WITH NORECOVERY so that is stays in "restoring" mode
  • keep ALL log backups from primary and copy them over to standby
  • restore all logs to the standby database WITH NORECOVERY
  • Run SSMS wizard to set up LS jobs

If you are using the LS jobs to backup/copy/restore the logs over make sure you are not missing any, also if you take a log backup outside of those jobs you will need those as well. The jobs will not copy or restore log backups that it doesn't create.

Jason Cumberland
  • 1,559
  • 10
  • 13
1

I have used a tool called uFTP to transfer huge SQL backup files over high latency links to get the initial full backup transferred to a secondary node for log shipping. You may want to take a full backup, copy it to the secondary server using uFTP, restore the database in no recovery, and then configure log shipping on the primary and use the "secondary database is initialized" option. Because of how fast uFTP should transfer the files you should be able to avoid the issues with everything being out of sync.

At worst, take a full backup of your database, copy it to the secondary with uFTP, then once that finishes, take a differential backup of your huge database and transfer that over with uFTP. Hopefully the combination of the high speed transfer and cutting down on the delta by using a differential as well, you should be able to get log shipping up and running.

Note: uFTP is a UDP based file transfer utility with an error checking mechanism built into the application since UDP is lacking error correction opposed to TCP.

dbush
  • 153
  • 8
tcnolan
  • 432
  • 2
  • 12
0

Make sure your log shipping target has the database restored with norecovery.

Go ahead and go through the setup process like you normally would.

When you get to the "Initialize Secondary Database" tab on the "Secondary Database Settings" window, make sure the "No, the secondary database is initialized" option is selected.

It will then skip the process doing a full backup and restore and will start with transaction log backups. If it doesn't have access to all the transaction log backups since your full backup, you can take a differential backup and restore that with norecovery to get things current.

KenJ
  • 196
  • 2
  • Thanks for the answer KenJ, but I am afraid this doesn't work. First of all the Secondary database has got to be in Standby/Restore mode. Another problem is that the 2 databases can never be true copies of each other because the by the time the copy reaches the secondary server the primary would be updated. Or maybe I am missing something? – SeanDav Oct 27 '10 at 13:05
  • Restoring the secondary with norecovery will leave it in the restore mode. – KenJ Oct 27 '10 at 13:26
  • I'm not sure what you mean about true copies. They will never match perfectly unless no transactions occur on the primary for a full log ship interval. It's okay if they don't match. You get them to match at recovery time by restoring the tail of the log from the primary or, if that's not available, by restoring the most recent available transaction log backup. – KenJ Oct 27 '10 at 13:29
  • hmmm. I am certain we tried something like this. The log shipping started working - in the sense that no errors were reported by the agents - **but** the changes were not actually being transferred - go figue! I will try again, just to test. – SeanDav Oct 27 '10 at 13:39
  • I repeated the process as suggested by KenJ with the same result. No errors but no restore. The message is that there isn't a file to be restored. Could this be caused because Primary Server is 2008 and Secondary is 2008 R2? – SeanDav Oct 27 '10 at 14:00
  • Is the message coming from the LSRestore* job on your secondary server? If so, is it worded like this: "Could not find a log backup file that could be applied to secondary database ''"? – KenJ Oct 27 '10 at 15:27