locked
Log shipping out of sync RRS feed

  • Question

  • Hi,

    secondary site Log shipping is out of sync for SQL server 2008 R2 Enterprise edition. Also, I don't the logs to recovery to point in time.

    What the steps and script which I need to use for recovering the standby database?

    Any help is appreciated!!!

    Regards,

    kccrga

    Tuesday, October 8, 2013 12:12 AM

Answers

  • Well I have fixed the issue by restoring the full backups and applying the logs on the secondary.

    • Proposed as answer by Ramesh Babu Vavilla Tuesday, October 8, 2013 6:33 AM
    • Marked as answer by Sofiya Li Wednesday, October 9, 2013 6:14 AM
    Tuesday, October 8, 2013 6:29 AM

All replies

  • If the secondary database server is out-of-sync, you need to find out what log backups have been restored. You need to query the msdb database for the list of log backups that have been restored

    select a.restore_date, a.destination_database_name, c.backup_size/1048576 as backup_size_MB, c.database_name as source_database, c.backup_start_date
    from restorehistory a
    inner join backupset c on a.backup_set_id=c.backup_set_id
    inner join backupmediafamily b on c.media_set_id=b.media_set_id
    where a.destination_database_name='name of database'
    and a.restore_date > 'date that you think the database is up-to-date'
    order by 1 desc

    Once you have a list of log backups that have been restored on the secondary database server, find out what is missing by looking at the backup history on the primary database server.

    SELECT database_name,Backup_type=   
         CASE  type             
        when 'D' then 'FULL'   
        when 'I' then 'DIFFERENTIAL'   
        when 'L' then 'LOG'   
        when 'F' then 'FILE/FILEGROUP'   
        when 'G' then 'DIFFERENTIAL FILE'   
        when 'P' then 'PARTIAL'    
        when 'Q' then 'DIFFERENTIAL PARTIAL'   
        END                
          , physical_device_name,backup_start_date            
                  
      FROM msdb.dbo.backupset a inner join msdb.dbo.backupmediafamily b   
      on a.media_set_id = b.media_set_id   
    Where database_name='name of database' 
    order by a.database_name,a.backup_start_date DESC   
    Obviously, you can only run this query if the primary database server is still online. Otherwise, you're out of luck. That's why you need to constantly monitor the status of your log shipping configuration.


    Edwin Sarmiento SQL Server MVP Edwin Sarmiento SQL Server MVP
    Blog | Twitter | LinkedIn
    SQL Server High Availability and Disaster Recover Deep Dive Course

    • Proposed as answer by Sofiya Li Wednesday, October 9, 2013 6:14 AM
    Tuesday, October 8, 2013 4:09 AM
  • Give me the steps how to recover?
    Tuesday, October 8, 2013 4:12 AM
  • Give me the steps how to recover?

    Hello,

    We dont do here spoon feeding. Did you actually followed the steps given by Edwin and tried to find out which trn log backup got missed and your Logshipping is out of sync.I am sure you not .I would appreciate if you  could please try it.

    If you are not able to find by any means or if some how your trn backup is deleted you have to configure log shipping again.

    Please try the steps given by Edwin and revert


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Tuesday, October 8, 2013 5:06 AM
  • Well I have fixed the issue by restoring the full backups and applying the logs on the secondary.

    • Proposed as answer by Ramesh Babu Vavilla Tuesday, October 8, 2013 6:33 AM
    • Marked as answer by Sofiya Li Wednesday, October 9, 2013 6:14 AM
    Tuesday, October 8, 2013 6:29 AM
  • if the Database size is very huge then take differentail backup followed with transaction log backup and re configure the LOg shipping

    Ramesh Babu Vavilla MCTS,MSBI

    Tuesday, October 8, 2013 6:34 AM
  • Well I have fixed the issue by restoring the full backups and applying the logs on the secondary.

    This is because I assume your DB was smal in size may be 5 -20 G ,but for large database like 200-500 G and above method by Edwin should be your approach.

    Also if you want to configure Loghipping again you can use diff backup followed by log backups to ease your work


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Proposed as answer by Sofiya Li Wednesday, October 9, 2013 6:14 AM
    Tuesday, October 8, 2013 6:38 AM