Log Shipping 101

chump! · July 17, 2007 9:00AM

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?

petey · July 17, 2007 11:29AM

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 &gt;= 5595000013096000001 AND a.first_lsn &lt; 5595000016342000001 AND a.type = 'L'

chump! · July 17, 2007 12:13PM

Can some one enlighten me as to how this can happen when setting up log shipping for the first time to a new database?

petey · July 17, 2007 12:32PM

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.

chump! · July 17, 2007 1:04PM

That's just it there is no other SQL Backup jobs other than the one I am attempting to setup via log shipping.

chump! · July 22, 2007 12:08AM

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?

chump! · July 25, 2007 9:33AM

Bump

petey · July 27, 2007 4:29AM

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)?

chump! · July 31, 2007 10:11AM

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?

petey · July 31, 2007 1:12PM

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.

chump! · July 31, 2007 2:44PM

Well I'm open for suggestions on next steps.

petey · August 3, 2007 4:16AM

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.

Log Shipping 101

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk