I’ve been tasked with moving a production database server that is involved in log shipping. I am not a DBA so this has been a rough road trying to get up to speed with this task. Just to get the mechanics down, I created a Test database on my primary server (Server 1) and added a couple of tables to it. I backed it up and restored it to my secondary server (Server 2) selecting the RESTORE WITH STANDBY option in order to get it created there. I then went back to my primary server and configured log shipping for my Test database. I added some rows to the tables in the primary server and then ran the job to back up the transaction log. I then went to the secondary server and ran the jobs to copy the transaction log and restore it. Everything worked PERFECTLY. I went back to the primary, made more changes, backed up the transaction log and then copied and restored it on the secondary. Again, it worked as expected and the databases are identical. Then I went through the steps outlined in these two links for how to move a log shipped database:http://sqlcat.com/sqlcat/b/msdnmirror/archive/2011/05/27/moving-the-standby-database-to-another-server.aspxhttp://www.mssqltips.com/sqlservertip/2873/how-to-move-a-sql-server-log-shipped-secondary-database-to-different-sql-server/
After performing the steps outlined in the links above, I went back to the primary server, made changes to my two tables and backed up the transaction log. I went to the new secondary server (Server 3), ran the jobs to copy and restore the transaction log (both ran successfully and returned no errors), however the changes made to the primary server (Server 1) never made it to my new secondary server (Server 3). There is a needle somewhere in this haystack that I am missing. I have gone through this exercise four times and cannot get transactions applied to my new secondary server. Here are the steps I took to try to change the secondary server:
- On the original secondary server (Server 2), right clicked on my Test database, selected tasks, restore, transaction log and then selected the most recent .trn file
- On the options page for restoring a transaction log, I left the default selection for recovery state which is “Leave the database ready to use by rolling back uncommitted transactions. Additional transaction logs cannot be restored. (RESTORE WITH RECOVERY)”. This option makes me very suspicious. But I cannot back up the Test database unless I get it out of Standby / Read-Only mode. I attempted selecting the second radio button for RESTORE WITH NORECOVERY but it just left the database in a “Restoring…” state.
- I can now back up my Test database on Server 2.
- Copied the back up to my new secondary server, Server 3.
- On the new secondary server, selected Restore Database and selected the backup file.
- On the options page, I selected the RESTORE WITH STANDBY option.
- On the primary server (Server 1), I remove the log shipping to the old secondary server (Server 2).
- I add log shipping to the new secondary server (Server 3). On the Initialize Secondary Database tab, I select “No, the secondary database is initialized”.
- The copy and restore jobs are successfully created on my new secondary server.
- Before doing anything else, I compare the contents of my two tables on my primary server (Server 1) and my new secondary server (Server 3). Everything matches.
- I then add a couple of rows to my tables in my primary server.
- I back up the transaction log on the primary server.
- On the new secondary server, I run the job to copy the transaction log. It successfully copies and I see it on the server.
- On the new secondary server, I run the job to restore the transaction log. It completes with success, but the changes are not reflected in the new secondary database. I receive no errors.
- When I check the log_shipping_monitor_secondary table on my new secondary server, it shows the correct value for last_file_copied, but last_restored_file indicates NULL.
There is radio button or checkbox or SOMETHING I’m missing in this process and I’ve beat my head against the wall trying to find it. I’m now on my fifth attempt at starting from scratch with a Test database. Right now, Server 1 and Server 2 are configured and working perfectly with log shipping. I’m ready to back up my database on Server 2 and restore it to Server 3 and add log shipping to Server 3, but I’m out of ideas at this point. What detail(s) am I missing?
EDIT: The reason I'm going about it this way is because the database on Server 1 is too large to backup and copy to Server 2. Server 2 and Server 3 are in the same datacenter so a backup of the database on Server 2 can easily be copied to Server 3.