Log shipping restore job doesn't restore

  • We have a restore job original created with Log Shipping (it launches sqllogship.exe). The job completed reporting success, but log files are not actually restored.

    Interestingly the History shows this:

    2015-03-05 11:52:48.75Found first log backup file to restore. Secondary DB: 'RPT_OLB_Support', File: 'G:\BIStandby2008R2\SQLBackup\User\VOBLogShippingDest\Support_TlogBackup_20150303205006.trn'

    2015-03-05 11:52:48.75The restore operation was successful. Secondary Database: 'RPT_OLB_Support', Number of log backup files restored: 0

    2015-03-05 11:52:48.75Deleting old log backup files. Primary Database: 'Support'

    2015-03-05 11:52:48.75The restore operation was successful. Secondary ID: 'ba980cb4-c600-4e82-903e-72553777ec09'

    If I run RESTORE LOG in SQLMS with Support_TlogBackup_20150303205006.trn it restores successfully. Then when I run the job, the history item above shows it correctly identifying the next log file to restore, then reports success without running the restore.

    What's going on?

  • need to see the complete history, but its restored 0 files. You likely have an LSN gap.

    on the 2 servers that are not working as expected please run the following query.

    select differential_base_lsn, differential_base_time

    from sys.master_files

    where database_id = DB_ID('yourdb')

    and type = 0

    group by differential_base_lsn, differential_base_time

    If the result is the same on the primary and the secondary,

    •take a differential backup on the primary

    •disable the LS agent backup job on the primary

    •disable the copy and restore jobs on the secondary

    •restore the differential to the secondary

    •Once done re enable any jobs

    •create a new table

    •run all jobs and check object exists

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • But in my experience, whenever I've run a RESTORE LOG command with anything other than the correct TRN file, I get a "too early" or "too recent" error. Yet here I can successfully run Restore Log manually!

    One clue is the History Messages show the Last Restored File as "None" and when I run sp_help_log_shipping_secondary_database on the Database last_restored_file is NULL. Similar restore jobs on this instance that do work do show the last file restored. If I tinker with the Log Shipping configuration to insert the last trn file I restored might that fix it?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply