re-sync log shipping database

sgray128 · October 14, 2008 9:10PM

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.

petey · October 15, 2008 3:08AM

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 &gt;= 933996000007504800001
  AND a.last_lsn &lt;= 933996000007504800001
  AND a.database_name = 'NGB01'
  AND a.type = 'L'

petey · October 15, 2008 3:15AM

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

sgray128 · October 15, 2008 12:48PM

Perfect, the 'differential' worked just fine.

Thanks!!

petey · October 16, 2008 1:46AM

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.

re-sync log shipping database

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk