Log Shipping to an Existing Database
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.
- Turn on log shipping, but don't add any targets
- Take a full backup of the primary database
- Move the database backup to the secondary server
- Restore backup
WITH NORECOVERY - Enable the secondary server as a log shipping target
- 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.
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.
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.