re-sync log shipping database

sgray128sgray128 Posts: 7
edited October 16, 2008 1:46AM in SQL Backup Previous Versions
Sirs:
I'm on my first day of log shipping - After several hours of successful shipping, I truncated the log on the main database (during sql maintenance, it was too big) and that broke the sync.

I'm getting this message now on the destination server:

NGB-WEB-02: Restore - Failed
============================

Restoring transaction log backup - Failed

This operation failed with errors.

Restoring NGB01 (transaction logs) from:
C:\Program Files\Microsoft SQL Server\MSSQL\logShipping\LOG_ngb01_20081014203001.sqb
SQL Server error
SQL error 3013: SQL error 3013: RESTORE LOG is terminating abnormally.
SQL error 4305: SQL error 4305: The log in this backup set begins at LSN 933998000000001600002, which is too late to apply to the database. An earlier log backup that includes LSN 933996000007504800001 can be restored.
SQL Backup exit code: 1100
SQL error code: 4305



Updating SQL Server information - Pending

Operation pending.


Is there any way to re-sync without deleting the entire job and recreating it? The DB is about 60 gb and takes a while to 'seed', I'd like to avoid that.

Comments

  • peteypetey Posts: 2,358 New member
    If you truncated the log without making a backup, you'll have to seed the database again. Does running the following return any results?
    SELECT * FROM msdb..backupset a
    INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
    WHERE a.first_lsn >= 933996000007504800001
      AND a.last_lsn <= 933996000007504800001
      AND a.database_name = 'NGB01'
      AND a.type = 'L'
    
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • peteypetey Posts: 2,358 New member
    Another option is to take a differential backup of the database, and apply it to the secondary database. However, this will only work if there have been no full database backups made since the full backup that was created to seed the secondary database.

    Steps:

    - stop the log shipping jobs on both the primary and secondary servers
    - take a differential backup of the primary database
    - restore this differential backup on the secondary database, with the NORECOVERY or STANDBY options
    - copy all the existing transaction log backup files that have not been restored on the secondary server to the 'processed' folder, since you would not need them anymore
    - start the log shipping jobs on both servers
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Perfect, the 'differential' worked just fine.

    Thanks!!
  • peteypetey Posts: 2,358 New member
    You should really avoid truncating the transaction log without backing it up.

    You seem to be using SQL Server 2000, which unfortunately does not prevent you from backing up the transaction log without a valid full backup.

    If you had encounterd a critical failure after truncating the log, you would only have been able to recover the database up to the time of the truncation. All log backups created after the truncation would have been useless.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.