February 19, 2013 at 5:02 pm
Although I have been using SQL Server for many years, I am merely a database developer and extremely green at anything DBA-related. I have found myself in a position where I am being asked to be a DBA and my first task is to move a production database. The wrinkle is that this production database to be moved is a secondary server in a log shipping configuration. I thought I had everything set up correctly on the new server and did a dry run of restoring the transaction log from a command prompt (simply using sqllogship.exe -Restore GUIDHere -server MyServer). I got the following error: "The restore operation cannot proceed because the secondary database is not in NORECOVERY/STANDBY mode". Here is the configuration (note: all servers are 2005):
Server A
- Production server with data warehouse database (I'll call it WarehouseDB)
- WarehouseDB is "massaged" here every night
- WarehouseDB is a primary database in a log shipping configuration
- Server B is configured as a secondary database on the Transaction Log Shipping page for WarehouseDB
- Server A is NOT going away
Server B
- Contains same data warehouse database as Server A (WarehouseDB)
- When viewing all databases on this server from SSMS, WarehouseDB indicates "Standby / Read-Only"
- When I go to the properties for WarehouseDB, the Status indicates "Stand By, Shutdown, Normal"
- Transaction log from Server A is restored here nightly using a job the prior DBA created which incorporates the standard copy and restore commands using sqllogship.exe along with some other steps
- Server B IS going away
Server C
- Contains same data warehouse database as Server A (WarehouseDB)
- WarehouseDB was created before I came into the picture and I'm not sure how it was created
- WarehouseDB was not in Read-Only mode so I went to Properties and Options and changed it to Read-Only
- Server C is replacing Server B
What I've done to configure Log Shipping:
- On Server A, I have added Transaction Log Shipping, referencing the Server C instance
- On the "Initialize Secondary Database" tab, I selected "No, the secondary database is initialized" since the target database exists (I think I chose the wrong option here?)
- On the "Restore Transaction Log" tab, I selected "Standby Mode" (perhaps I chose the wrong option here as well?)
- I scripted the job the prior DBA created on Server B and excuted it on Server C (changing transaction log GUIDs where necessary on the steps that copy and restore the transaction log since Server C got a new GUID for the transaction log).
- The copy and restore jobs were successfully created on Server C but they are disabled. The corresponding steps in the big job (referenced in the bullet above) that do the copy and restore utlize the commands created in these jobs.
- Although this job has been set up, I'm testing the process by manually executing the restore from a command prompt. We are having firewall issues as well so for now, I am just going to Windows Explorer on a server in our datacenter and copying the .trn file from Server A to Server C. Then I connect to Server C and attempt the restore using sqllogship.exe
Like we've all done, I've spent quite a bit of time on Google researching backup, restore, transaction logs, etc. The more I read, the more I realize I don't know. Do I need to start over from scratch in creating WarehouseDB on Server C and let the log shipping configuration page create the database? I selected Standby mode, but should I have selected No Recovery Mode? I probably have no business doing any of this but the buck absolutely stops with me. They have been without a DBA for months and then they hired me, hoping I could pinch hit with some DBA tasks before I just assume a DBD role. In a perfect world, they will hire a DBA at some point. I'm beginning to think they (and I) did not chose wisely! Extremely nervous about this....
Thank you
February 20, 2013 at 1:14 am
Am I correct in saying that the WarehouseDB that you're trying to restore logs to was online and usable before you started? You set the DB to read only (ALTER DATABASE ... SET READ_ONLY...)?
If so, then to set that up as a log shipping secondary, you need to drop the DB, restore a new full backup either with STANDBY or NORECOVERY, then you can restore logs. If a DB was recovered and brought online you cannot then restore log backups to it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 20, 2013 at 8:59 am
GilaMonster (2/20/2013)
Am I correct in saying that the WarehouseDB that you're trying to restore logs to was online and usable before you started? You set the DB to read only (ALTER DATABASE ... SET READ_ONLY...)?
Server C, where I'm attempting to restore the logs, was actually configured before I came on board. Technically, it is online and usable, but this server is not officially "online in production". Once I can get the transaction logs updating correctly, then we'll turn off Server B and Server C will be operating in a production capacity. I went to WarehouseDB on Server C, did a right click to get to properties, went to Options and then changed the database to Read Only.
I will follow your suggestion to drop the database and restore a new full backup with STANDBY.
Correct me if I'm wrong.... Once I get this database restored and do a dry run of restoring the transaction log, I need to do that daily, until Server C is live and in production, right? Obviously if I don't keep this up, then WarehouseDB on Server C will not be in the proper state. For example, if we decide we want to switch to Server C in two weeks I have two choices: 1) do a daily restore of the transaction log on Server C so it is current and ready to go in two weeks or 2) the day of go-live, drop and restore the database to ensure it is current.
Thank you!!
February 20, 2013 at 9:08 am
*Daily* log backups?
Maybe take a read through this - Managing Transaction Logs[/url]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 20, 2013 at 9:34 am
Server A processes data in WarehouseDB at midnight every night. As soon as that is done, the transaction log is backed up. Server B then has a job that copies that transaction log from Server A to Server B and restores it on Server B. That's the process I need to mimic on Server C.
Thank you for the link to the article.
February 20, 2013 at 10:13 am
Again, daily log backups??? Sure that's good enough?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 20, 2013 at 10:44 am
Good question. There is another server in this whole configuration, let's call it Server 1. That's what the end users are hitting and updating. Server A copies data from key tables in Server 1 to the WarehouseDB in Server A and then the data is massaged on Server A and ultimately ends up on Server B. Transaction log is backed up on Server 1 every 3 hours. Web front ends hit Server 1 and Server B, but not A. Server A sits idly by until the next nightly processing window. I know... this is all rather confusing.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply