February 25, 2013 at 10:42 am
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:
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.
Thank you
February 26, 2013 at 11:53 pm
HI , take my advise if you like then follow this
even your posting create so much of confusion after reading so many times i am giving reply first read the reply and in the end follow the logic at the end of this post.....
First of all you chill out take a T&D server [or] in your laptop configure the log-shipping primary and secondary databases coz Not to mess up your brain with production server until u are trying to work out these modifications in production server you always feel frustrated every time your attempt fails
1. if i'am correct you have a production servers " 1 & 2 " with database name [Test Database ] you added couple of tables backed up and restored it to secondary server (Server 2 database) selecting the RESTORE WITH STANDBY option in order to get it created there also checked properly sync of both 1&2 server database in which SQL Server log-shipping is been configured and running fine.
up to tz fine
2.whether you removed the server 2 database from the log-shipping and added server 3 database in log-shipping ?????????????? make sure it happened properly
-----[1] do u want server 1 database
server 2 database
server 3 database
All in log-shipping as primary , secondary ,and monitor give clarity in this ????????????? if u want to configure like this ....
if not ok and see below ----------------------------------------------
----- from here
[2] if u want to add server 3 to server 1 in log-shipping
configuration
"" whether you removed ?? server 2 database from secondary
database in log-shipping after removing server 2 database only you have added server 3 database as secondary database to log-shipping make sure
in this.. then you get clarity """
3. if you have removed server 2 database and added server 3 database as secondary database in the log-shipping configuration,after adding then you created couple of tables in server 1 database , and
-------- [1] you have run the ls_backup job in server 1 database
[2] you have run the ls_copy and ls_restore jobs in new
secondary database server 3 database.
Even though the changes which you made in server 1 database are not applying in the sever 3 database [ new secondary ]...
follow this
" before u removing server 2 database and adding server 3 database "
1. http://www.mssqltips.com/sqlservertip/2301/step-by-step-sql-server-log-shipping/
2. at removing secondary database server 2 database and click ok in log - shipping configuration wizard [ right click on data base --> task--> log-shipping ]
then again go to the server 1 database [ right click on data base --> task--> log-shipping ] add server 3 database as secondary database then your log-shipping primary and secondary will ready and make any changes in the server 1 database and run ls_backup job in server 1 database and run ls_copy , ls_restore jobs in server 3 database .then both servers will be in synchronization
-------------------------------------------
logic :---- for ur requirement simply. don't confuse here ...
-------------------------------------------
three servers 1,2,3 database
----[1] server 1 database ----->server 2 database in log-shipping you made changes in 1 and checked in 2 whether it is applying or not yes it is applying
----[2] you want to remove server 2 [database ]from log-shipping and add make changes in server 1 database and then configure log-shipping to server 1 database [to ]server 3 database then both will be in synchronization.
if every thing i working fine in T&D [or ] in ur laptop then go in to the production server ....
Thanks
Naga.Rohitkumar
March 6, 2013 at 4:15 pm
Thank you for your reply Naga. Yes, it is a lot of information. I tried to be real methodical but it just made for a very long post! The reason for my test was in preparation for a big database move that was not possible over the network. Turns out we had the option of backing up to a 1TB USB device and had that shipped to our other data center out of state. I was then able to restore the database and establish log shipping to it and all is good now.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply