locked
Log Shipping & Filegroups RRS feed

  • Question

  • Hi Guys,

    I need some help for my Log Shipping database please.

    I have a database X on the production server and on the log shipping standby server (Read-only). The logs on the production server is shipped to the standby server. The standby server restores the logs through a schedule job.

    Now what happended is couple of days back we created another filegroup (Secondary) on the production server for the database X to keep our history separated from the most current data. When we created the secondary filegroup our logs on the log shipping server cannot restored. We investigate the problem and found that there is no secondary filegroup attached to our database X on the log shipping server, which is obvious.

    My question now is there a way we can create a secondary filegroup for the database X on the log shipping server without breaking our existing log shipping? Do we must restore the full backup of the database X on the log shipping server in order to keep the transaction logs applied on the log shipping database X?

    Please help.

    Thanks.

    Wednesday, May 7, 2008 10:01 PM

All replies

  • Here is a scenario:

    1. Stop logshipping.

    2. Rename standby db

    3. Restart logshipping with full synch


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012
    • Edited by Kalman Toth Sunday, September 30, 2012 6:57 PM
    Wednesday, May 7, 2008 11:53 PM
  •  SQLUSA wrote:

    Here is a scenario:

     

    1. Stop logshipping.

    2. Rename standby db

    3. Restart logshipping with full synch

     



    Thanks for the reply SQLUSA.

    1. My Logshipping is already stopped. I can't restore any further logs to my logshipping database since the day we have created a new file group on the production server. Production server now has 3 sql database files (Primary mdf, Secondary ndf and Transaction log ldf) & our logshipping has 2 sql database files at this stage (Primary mdf and transaction log ldf).

    2. Renaming the standby db! Can you please elaborate this point as I don't know what is exactly going to happend after I rename my standby db.

    3. Restart logshipping with full sync, do you mean that I have to restore the last nights full database backup on my logshipping server and then re-establish the logshipping.

    Please let me know. Thanks.
    -gladiatr
    Thursday, May 8, 2008 3:31 PM
  • 2. You rename it like zzzDatabaseA for archiving and later removal.

    3. You have synch to create the standby database brandnew.

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012
    • Edited by Kalman Toth Sunday, September 30, 2012 6:58 PM
    Thursday, May 8, 2008 7:36 PM
  • Just do it manually after copy tran log to secondary ================================== RESTORE LOG [Manish] FROM Disk= N'C:\Program Files\Microsoft SQL Server\MSSQL10.VISHNU\MSSQL\Copy\Manish_20110919170940.trn' WITH MOVE 'manishs' TO 'C:\Test_Data.NDF', ------===================New Ldf or NDF file location STANDBY = N'C:\Program Files\Microsoft SQL Server\MSSQL10.VISHNU\MSSQL\Copy\Manish_20110919171028.tuf', NOUNLOAD ,STATS = 10 GO
    manish
    • Proposed as answer by Karunakar Kotha Thursday, September 22, 2011 3:17 AM
    Monday, September 19, 2011 5:34 PM