locked
Log Shipping setup RRS feed

  • Question

  • Over the weekend one of our production databases was restored  (without breaking the log shipping first)

    I am now using the sql server manahement studio to re-establish log shipping.

    This is sql server version 10.0.2531

    Step 1 was to disable the log shipping and remove the current standby database. Checked all jobs and database files were gone as well.

    Step 2 was to use the management studio and setup logshipping. This worked fine without errors. The database and all jobs are created.

    Issue is when the restore job on the standby site tries to apply log files it is looking for transaction log files from a couple of days ago and not the ones that came after the backup that was created by the process to setup log shipping. I created a new table in primary to check that a new backup was actually being created.

    Anybody have any ideas why it would look for transaction logs that are 2 days old?

    Jack

    Monday, February 13, 2012 1:03 AM

Answers

  • OK

    Answer was really simple in hindsight

    After the restore we should have deleted the transaction logs from the primary site that did not belong to the restored database. Now logshipping was copying all transaction logs over to the standby and going up from oldest to newest until it found one with LSN it thought it could use. This one belonged to the trashed database so the restore was quite rightly refused and restore stopped.

    Jack

    • Proposed as answer by Peja Tao Wednesday, February 15, 2012 2:13 AM
    • Marked as answer by Peja Tao Wednesday, February 15, 2012 2:13 AM
    Tuesday, February 14, 2012 1:41 AM

All replies

  • Jack

    The job on primary should copy a log file into a directory from within a standby database takes it to recover? How do you know that it looks for old backups?


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Monday, February 13, 2012 6:10 AM
  • Hi,

    As work around try to setup a new Log Shipping by restoring the ful backup and do the other steps.

    OR (Hopefully you found what you need).

    check this link and the link about GAPS in backup.

    http://social.msdn.microsoft.com/Forums/en-US/sqldatabasemirroring/thread/9459737a-e8a9-4dcb-8a9c-96e1406368e7

    I hope this is helpful.

    Elmozamil Elamir

    MyBlog


    Please Mark it as Answered if it answered your question
    OR mark it as Helpful if it help you to solve your problem
    Elmozamil Elamir Hamid
    http://elmozamil.blogspot.com

    Monday, February 13, 2012 6:11 AM
  • I know from the error it is trying to apply an old transaction log (the timestamp is in the name)

    After I blew away the whole logshipping setup and recreated it from scratch, I could confirm it used a fresh backup as I had created a test table to check.

    The logshipping jobs are created just fine and the transaction logs on primary get backed up and copied across to the standby site. just the restore job errors because it is trying to apply wrong transaction log.

    I can manually apply the correct transaction log.

    Is there some metadata in the master/msdb database that was left behind when primary was restored before blowing away the log shipping setup?

    Where does the restore job get it's information from on what to restore?

    Jack

    Monday, February 13, 2012 9:09 PM
  • OK

    Answer was really simple in hindsight

    After the restore we should have deleted the transaction logs from the primary site that did not belong to the restored database. Now logshipping was copying all transaction logs over to the standby and going up from oldest to newest until it found one with LSN it thought it could use. This one belonged to the trashed database so the restore was quite rightly refused and restore stopped.

    Jack

    • Proposed as answer by Peja Tao Wednesday, February 15, 2012 2:13 AM
    • Marked as answer by Peja Tao Wednesday, February 15, 2012 2:13 AM
    Tuesday, February 14, 2012 1:41 AM