none
Log Shipping Not Working RRS feed

  • Question

  • I have been trying to setup Log Shipping between two servers in our domain. I have followed the steps in the documentation, but the final step of restoring the transaction logs on the Secondary Server Instance database never happens. We are using the pubs database to setup this initially. The Secondary Instance/database is in Stand-by/Read-Only mode

    Now there are 3 jobs that are mentioned during setup.

    - (LSBackup_pubs)  This job backs up the trans log files (I always get job succeeded and it does)

    - (LSCopy_mlst4_pubs) This copies over the trans log files to the other server (I always get job succeeded and it does)

    - (LSRestore_mlst4_pubs) This is the job that is suppose to do the restoration (I always get job succeeded but there is no restoring of the Transaction Log Files on the Secondary Server).

    So there is no problem in taking the backup and copying the files. It is in the final step of restoring on the database on the Secondary Instance.

    Thanks for any and all help

    Wednesday, July 10, 2013 12:12 AM

Answers

  • Have you checked the restore history at the secondary server, run below command to check this:

    ==========================================================================

    Select destination_database_name,restore_date [Restore Date],bs.database_name [Source Database Name],bs.server_name [Source Server],bs.backup_start_date,bmf.physical_device_name [Restored from Location]

    from msdb.dbo.restorehistory rh

    inner join msdb.dbo.backupset bs on bs.backup_set_id=rh.backup_set_id

    inner join msdb.dbo.backupmediafamily bmf on bmf.media_set_id=bs.media_set_id

    where destination_database_name = 'database name' order by restore_date desc

    ==========================================================================

    For testing log shipping try to apply the logs manually based on the output came from above query

    Cross check the permissions for the service account on the shared folder as well as on the secondary folder.


    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Wednesday, July 10, 2013 12:53 PM
  • Try the restore command manually on the secondary server and see if it gives any errors. It should use either NORECOVERY or STANDBY=... option, not both. Initial full backup should also be restored with that option.

    It's really strange that job succeeded. Look into sql error log on LS server - are there any errors?

    Wednesday, July 10, 2013 12:59 AM

All replies

  • Try the restore command manually on the secondary server and see if it gives any errors. It should use either NORECOVERY or STANDBY=... option, not both. Initial full backup should also be restored with that option.

    It's really strange that job succeeded. Look into sql error log on LS server - are there any errors?

    Wednesday, July 10, 2013 12:59 AM
  • What version are  you using? I did testing on SQL Server 2005,2012 and it works just fine.

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

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Wednesday, July 10, 2013 5:38 AM
  • Have you checked the restore history at the secondary server, run below command to check this:

    ==========================================================================

    Select destination_database_name,restore_date [Restore Date],bs.database_name [Source Database Name],bs.server_name [Source Server],bs.backup_start_date,bmf.physical_device_name [Restored from Location]

    from msdb.dbo.restorehistory rh

    inner join msdb.dbo.backupset bs on bs.backup_set_id=rh.backup_set_id

    inner join msdb.dbo.backupmediafamily bmf on bmf.media_set_id=bs.media_set_id

    where destination_database_name = 'database name' order by restore_date desc

    ==========================================================================

    For testing log shipping try to apply the logs manually based on the output came from above query

    Cross check the permissions for the service account on the shared folder as well as on the secondary folder.


    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Wednesday, July 10, 2013 12:53 PM
  • Hi

    any progress on this? I have about the same issue.

    We have instances geographically separated. They cannot connect to each other and the backup files are sync'd using data domain replication.

    The restore job on the secondary finds the correct log file to restore but doesn't restore it. The job finishes successfully.

    Manual restore works but then during the subsequent run the LS restore job just goes to the next backup file and stops there. So everything is working, it just doesn't restore.

    This is SQL2008R2 10.50.4270

    Regards

    Thierry

    Friday, January 10, 2014 9:58 AM
  • ok, thanks all for replying! That's just sarcasm :)

    For anyone interested: Brent Ozar's team to the rescue! Thx Jes!

    http://www.brentozar.com/archive/2013/03/log-shipping-faq/#comment-670810

    Monday, January 13, 2014 9:47 AM