SQL Server Performance

Home grown log shipping, need some help please!

Discussion in 'SQL Server Log Shipping' started by niall.porter, Sep 21, 2006.

  1. niall.porter New Member

    Hi all,

    We have a pretty basic, home-grown log shipping setup. Until now, if for some reason the secondary server is unable to apply the latest log backups (usually because it can't get exclusive access to the target DB) it fails and that database has to be fully re-synced from the primary.

    I am testing a modified system which uses tables in the msdb databases on both servers which have a list of the databases in the log shipping and flag against each one which is set to 1 if the last lot of logs were applied successfully or 0 if they were not. If the primary sees a 0 on either it's own or the secondary's table for a given DB, it appends the transactions onto the current set (specifying WITH NOINIT). It will keep doing this until it sees a 1 on both the status tables for that database, indicating the last lot of logs were applied OK. When it does it backs up the logs using WITH INIT, which overwrites the current set.

    The issue is that if the secondary has to apply more than one set of logs (written using WITH NOINIT), then once that has succeeded the next time it tries to apply a single set (written using WITH INIT) it fails with the message "The log in this backup set begins at LSN xxxxxx, which is too late to apply to the database. An earlier log backup that includes LSN 1247592000000017600001 can be restored. [SQLSTATE 42000] (Error 4305)".

    Anyone got any ideas? Should I be using WITH FORMAT instead of WITH INIT for normal (i.e. when it's not appending logs) backups?
  2. niall.porter New Member

    OK I've done some more testing. The command to restore the logs to the secondary server when there are multiple log backups in the file is this:

    RESTORE LOG dbname FROM DISK = 'I:Standbydbname_LOG.BAK' WITH STANDBY = 'H:SQLServerMSSQLDatadbname_undo.ldf'

    Through testing on Northwind I've come to the conclusion that this only restores the first set of logs in the file and any subsequent sets are not restored. I've checked Books Online and some websites and books but I can't see any thing that will help. Is there an argument I can give to RESTORE LOG to get it to apply ALL the log backups in the file?
  3. satya Moderator

    Do you see any error during the next set of restore?
    During the period are you performing any activity on NorthWind in order to generate the Transaction log.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  4. niall.porter New Member

    Just to clarify the testing, the sequence of events ran thus:

    - added a record to the "Territories" table in Northwind
    - did a BACKUP LOG Northwind TO DISK = ... WITH INIT
    - added another record to the same table
    - did a BACKUP LOG Northwind TO DISK = ... WITH NOINIT

    Took the log backup file onto the secondary server, then

    - did a RESTORE LOG Northwind FROM DISK = ... WITH STANDBY = ...

    I checked the "Territories" table in Northwind on the secondary server and only the first of the records I added on the primary server was in there. I did another BACKUP LOG... WITH INIT on the primary and tried to restore that on the secondary exactly as above but it failed claiming out of sequence LSN numbers. I had to do RESTORE LOG Northwind FROM DISK = ... WITH FILE = 2, STANDBY = ... on the first file before the second one would restore.

    All I've proven here is that RESTORE will only restore one backup from the media at a time. What I need it (or something else) to do is to just go through all the backups in a file and apply them in order.

Share This Page