What are the challenges you face when working across database platforms? Take the survey

Warning 170: Log files are not in sequence:

Pat ShawPat Shaw Posts: 4
edited October 6, 2009 1:02PM in SQL Backup Previous Versions

I have built an independent log shipping process for 30+ databases that utilizes SQL Backup for compression purposes. This has been ticking along nicely for months now until last week when I started getting warning messages and the logs for two databases are now not being automatically restored. A sample of the message is as follows:

Warning 170: Log files are not in sequence: 'MyDB_20091005011719.sqb' and 'MyDB_20091005013054.sqb'

exitcode 170

When I look in the BAK folder for each of the two databases the log backups are stacked up and cannot restore in unison. If I execute the backup command which runs in a loop to pickup each file I can restore the backups but only one at a time, which means that if there are 20 files waiting for restore I need to execute the command 20 times when once should be adequate. It appears that as soon as there is more than one file for restore the error takes effect. This has started to happen on just two databases while the other 20+ operate normally.

I read a related article on the forum which suggested running the SQBHeaderFix utility. I did this and the backups were rendered useless which meant a full restore had to be conducted.

Can anyone please tell me why this has begun to happen.



  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Thanks for your post.

    What sometimes happens with regards to log backups is this: When the log backup is complete, SQL Backup asks SQL Server for the LSN information and writes it to the SQL Backup file header. When doing RESTORE LOG with a wildcard, SQL Backup orders the log restores according to the LSN information in the SQL Backup file header. If SQL Backup could not get this information from the SQL Server when it backed up the log, for instance, if the MSDB database was corrupt of a deadlock had occurred, then the log shipping restore will break when it encounters the file.

    A manual restore of the individual headerless file, however, is still possible. This is only because SQL Server puts the LSN information at the tail-end of the backup file. Otherwise, SQL Server would return the same LSN error that you get from SQL Backup.

    If this does happen, then you should try restoring the log files from the log shipping share in date order, which would save you the trouble of reseeding the whole log-shipped database.

    To detect any damaged log backups in your log shipping share, you can use the RESTORE SQBHEADERONLY command against the file or files. This will show what's actually in the SQL Backup file header.
Sign In or Register to comment.