SQL Server Performance Forum – Threads Archive

Log – Shipping Problem to the secondary server

I have an SQL Server 2005 (ServerA) hosting the application DB(DatabaseA) accessed by many customers who insert, update, delete , select data.
I need another database (DatabaseB) on another SQL Server 2005 (ServerB) for development purposes but i need also this db to be updated with the real data of DatabaseA.
I decided to implement log shipping between DatabaseA (primary) and DatabaseB (secondary).
When I change the schema of a table in DatabaseB (e.g. in table T1 I add a nullable column) then the log shipping doesnt work (ie if i insert a record in ServerA.DatabaseA.T1, there is no insertion into the ServerB.DatabaseB.T1)
Could you please tell me if there is any answer to this problem?
Should I choose another feature of SQL Server 2005 and not log shipping? If yes, which is the feature that meets my requirements?
Thanks

---

The DatabaseB must be in STANDBY mode in order to enable the Log shipping to restore the primary server logs, you shouldn’t update any data on database B that will break the log shipping. refer to books online for more information on log shipping architecture. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

Thank you very much for your answer but the purpose of having DatabaseB is to change its schema, to insert data during the development phase (for testing purposes)and of course I need to have the real data that customers insert into DatabaseA (that s why i implemented log-Shipping). Could you please tell me if there is another feature of SQL Server 2005 that could help me to have a real-time Database (DatabaseA),to send its data to another database (DatabaseB), change the data and schema in DatabaseB during the development phase and, after testing, if stable, I would like to change the schema of DatabaseA to be the same as DatabaseB Thank you very much
George

---

If you want to use the Database B for development purpose then you can define the log shipping to restore as a seperate database and then take backup of that database which will be used for development services. In any case if you try to update the secondary database in log shipping, it will break the chain. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

Thank you very much.
Concerning your first answer, I face this problem:
The backup to the primary and the copy process from the secondary have no problem
But during the restoration process I get this error
The restore operation cannot proceed because the secondary database ‘DBThales’ is not in NORECOVERY/STANDBY mode.(Microsoft.SqlServer.Management.LogShipping)
Could you please tell me where is the problem?
I tried to configure the secondary server (through the primary SSMS) in order to use NoRecovery or StandBy
Why I get this message?
Thanks for your help

---

Best way forward is to stop log shipping and restore a fresh database backup from primary to secondary using Query Analyzer with STANDBY clause in RESTORE statement. Then enable the LS in order to continue the process, then from Secondary server you can perform the backp for development purpose. Refer to Books online for more information on STANDBY clause. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

Yes, but my secondary database is in restore or standby mode during the log shipping (I cant see its content, its data etc) and the Backup operation fails in this database because it is in restore mode.
I want to ask you this:
First of all, I got a backup from primary and I restored it in the secondary db with NORECOVERY option. The secondary database was in restore mode (locked, I couldnt see its content). I started then the log shiiping process (the secondary db was in restore mode and I cant see its content). After two hours of log shipping I wanted the secondary db to be usable. So I executed RESTORE DATABASE secondaryDB WITH RECOVERY and I realized that even if the restore-job history was succesful, no log had been restored to the secondary database. What was wrong.
1. Is it correct the secondary Db to be always locked (due to restore/stand by mode)?
If yes, in the case that the hard disk of primary server fails and I have no t-log availiable , which is the command I have to execute on secondadry db to stop the restore mode and make it usable without losing the changes?
Thanks

---

Yes, the secondary server datbase must be in NORECOVERY (with no usable status) or STANDBY(for readonly process) in order to continue the log shipping process. As I referred above you can keep the secondary server in standby and then continue the backup for this on a seperate database server for your development purpose. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

satya, the copy and backup jobs terminate succesfully but the restore job always returns the following message Error: Could not apply log backup file ‘\secServerSharedFolderSecondaryPrimaryDB_20060918071600.trn’ to secondary database ‘SecondaryDB’.(Microsoft.SqlServer.Management.LogShipping) ***<nl/>2006-09-18 10:18:28.44 *** Error: During startup of warm standby database ‘SecondaryDB’ (database ID 11)its standby file (‘\secServerSharedFolderSecondarySecondaryDB_20060918071828.tuf’) was inaccessible to the RESTORE statement. The operating system error was ‘5(error not found)’. Diagnose the operating system error<c/> correct the problem<c/> and retry startup.<nl/>RESTORE LOG is terminating abnormally Have you any idea about what caused this problem?
Thanks

---

I think before restoring the Database on Standby Mode through QA/EM, you should stop the Logshipping job, make a slight change in the relevant Log shipping Maintenance Plan – Enable use the existing Backup and mention the Backup File name with its location and restart the Logshipping job once again. Regards, Arindam Ganguly

---

Has anything changed on Secondary server since the last Transaction log backup? Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

]]>