Competition: What’s your favorite Redgate tool? Enter now.

Log Shipping 101

chump!chump! Posts: 8
edited August 3, 2007 4:16AM in SQL Backup Previous Versions
I have been working with the Log shipping "feature" using the log shipping wizard with the latest version of sql backup 5. We are attempting to setup log shipping to a remote location across a vpn. The bandwidth is 3MB and the compressed full sql backup is 5GB and takes around 6 hours to copy to the remote location.

I have been working for two weeks testing and working this board. Now yesterday I was able to get past the "issues" of the 5.0 wizard and get shipping to finish successfully; however now I am receiving an error when attempting to restore the transaction logs. The error is:

SQL error 3013: RESTORE LOG is terminating abnormally.

SQL error 4305: The log in this backup set begins at LSN 5595000016342000001, which is too late to apply to the database. An earlier log backup that includes LSN 5595000013096000001 can be restored.

Anyone?

Comments

  • peteypetey Posts: 2,358 New member
    The error indicates that there are earlier log backups that need to be applied, before the current log can be restored. I would suggest checking the msdb..backupset/backupmediafamily tables to find those earlier logs. E.g.
    SELECT b.physical_device_name FROM msdb..backupset a INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
    WHERE a.last_lsn >= 5595000013096000001 AND a.first_lsn < 5595000016342000001 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
  • Can some one enlighten me as to how this can happen when setting up log shipping for the first time to a new database?
  • peteypetey Posts: 2,358 New member
    Perhaps an exisitng SQL Server Agent job continued to take regular transaction log backups while you were setting things up? The file names ought to tell you if the backups were created using SQL Backup.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • That's just it there is no other SQL Backup jobs other than the one I am attempting to setup via log shipping.
  • So is there an issue with the length of time it takes to copy the database or to restore the database that when the first transaction log restore kicks off it thinks it's out of sync?

    I have setup and tested a small database and that works fine but when setting up log shipping with the large database (45 GB - 4.5 GB compressed), the transaction log restore errors every time.

    Reaching the end of my rope here. Anyone?
  • peteypetey Posts: 2,358 New member
    Is this what is happenning:

    - a full backup is made of the production database
    - this is copied to the standby server, which takes 6 hours
    - the database is restored on the standby server
    - while the copying was in progress, transaction log backups were made of the production database
    - these logs were then copied to the standby server, and restores were attempted but failed with the error message 'The log in this backup set begins at LSN ...'
    - checking the msdb..backupset table on the production server indicates that no other transaction log backups were made

    Did you try restoring the transaction log files individually, starting from the first file? Also, do any of the issues mentioned here apply (http://support.microsoft.com/kb/272093)?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Close except for the transaction log part.

    The logshipping wizard does not create a transaction log job until the backup has completed and since it takes 6 hours to complete there are 24 missing transaction log files.

    I have tested and verified this is the case.

    Is there anyway to have the wizard start the transaction job at the begining of the database backup?
  • peteypetey Posts: 2,358 New member
    Is there anyway to have the wizard start the transaction job at the begining of the database backup?
    No. It wouldn't help as the first transaction log backup file that you need to restore on the standby server can only be created once the full backup and copy has completed.

    The problem does appear to be due to a broken chain of transaction log backups. Possibilities include log backups that were made that have not been copied over to the standby server, a non-logged operation was carried out or the recovery model was changed in between backups.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Well I'm open for suggestions on next steps.
  • peteypetey Posts: 2,358 New member
    I would suggest checking the sequence of backups again. Look for the details of the full database backup that was created. Then check if you have moved all subsequent transaction log backup files to the standby server.

    If the files are all there, then look for the first transaction log file that should have been restored. The header may be incomplete, thus throwing off the sequence of files that should have been restored. Restore that file manually, and if that succeeds, move that file to the processed folder. The scheduled restore task should run without problems then, assuming no other files have incomplete headers.
    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.