Author: Alexei Khalyako
Technical Reviewers: Murshed Zaman; Prem Mehra
Editor: Beth Inghram
Recently a customer approached me with a request for help. They had set up log shipping between two servers that were installed in two different geographical locations. The problem was the following: Over time their database grew, and they saw that they were approaching the limit of available disk space on the secondary. The customer identified the following goals:
1) They needed to avoid moving data between the primary server and the secondary over the network, simply because moving their data over a very slow network could take over a week.
2) Because they had a bigger server to replace the old one, ideally they wanted the new server become the new secondary.
After some investigation, we found a very simple solution for this problem. These are the steps to follow if you have similar constraints:
1. Pause log shipping by stopping jobs on the primary (let’s call it Server 1) and the secondary (Server 2) responsible for alerting, database backup, and restore.
2. On the secondary (Server 2), restore the STANDBY copy of your primary database. You can do this by right-clicking on the database name in SQL Server Management Studio, selecting Tasks, clicking Restore, and then clicking Transaction Log. The following picture illustrates the menus.
In the new dialog box, Restore Transaction Log, make sure that the option Point in time shows Most recent possible.
After this action is complete, the database is no longer in STANDBY mode.
3. Create a full backup of the secondary database.
4. Restore the backup on the new server (Server 3) with the RESTORE WITH STANDBY command.
5. On the primary server (Server 1) remove the previous secondary server reference (that is, the reference to Server 2) in the Transaction Log Shipping properties.
6. In the same window, add the new secondary:
a. Click Add.
b. In the Secondary Server Instance dialog box, click Connect and then click the new server (Server 3) instance name.
c. Select the database that you restored with the STANDBY option.
d. Make sure the option No, secondary database is Initialized is selected.
7. Your log shipping works again and now the secondary database is on the new server. To test it, you can ship the log by right-clicking the database name, pointing to Tasks, and then clicking Ship Transaction Logs.
If your server database has data residing on the internal disks and the database tends to grow, sooner or later you may run out of disk space and find the approach described above useful.