Multiple file log restore fails on earliest log

ihfrickihfrick Posts: 6
edited July 2, 2005 11:39AM in SQL Backup Previous Versions
I am doing log shipping to a standby server, starting with a full restore then applying the transaction logs with SQL Backup every hour thereafter. Typically two log files are waiting to be applied by the time the log restore job first runs, at which point I get this error (pathnames removed for brevity):

Restoring transaction logs for database Bigben from "\transactionlogs\*.sqb"
Processing log group 176426093 ...
\transactionlogs\LOG_(local)_Bigben_20050701 030002.sqb
\transactionlogs\LOG_(local)_Bigben_20050701 040003.sqb Completed.
Moved LOG_(local)_Bigben_20050701 030002.sqb to \transactionlogs\processed\.
Moved LOG_(local)_Bigben_20050701 040003.sqb to \transactionlogs\processed\.
Msg 3259, Level 16, State 1, Server xxxxxxx, Line 1
The volume on device 'SQLBACKUP_176389181' is not part of a multiple family media set.
BACKUP WITH FORMAT can be used to form a new media set.
Msg 3013, Level 16, State 1, Server xxxxxxx, Line 1 RESTORE LOG is terminating abnormally.
Process Exit Code 0. The step succeeded.

I use the following command (pathnames removed here as well)

SqlBackupC.exe" -SQL "RESTORE LOG [Bigben] FROM DISK = '\transactionlogs\*.sqb' WITH STANDBY = '\UNDO_Bigben.DAT', MOVETO = '\transactionlogs\processed'"

If I go back and put the earliest log file (LOG_(local)_Bigben_20050701 030002.sqb) in the directory by itself and re-run the job, it works fine. I can then take any number of logs that accumulate thereafter, put them in the restore directory, and it will restore all of them at once without a problem.

Very puzzling. Moreover, I'm not using multiple media sets or media families to my knowledge, just a regular SQLBackup command with the COPYTO option to create log backups.

Any advice greatly appreciated! Thanks.
-Rick
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    Looks like something may be amiss in the header. Could you pls post the header information of the first backup file (LOG_(local)_Bigben_20050701 030002.sqb) using the RESTORE SQBHEADERONLY ... command? Thanks.

    As you may have noticed, there is a bug in SQL Backup in that it does not report SQL Server errors, only errors that it encounters itself. That is why the status code returned is 0 (successful) even though this was not the case. This will be fixed in a future version.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • ihfrickihfrick Posts: 6
    I'll give you the headers for both backups. Should different files have the same Backup Group ID? Thanks!

    Output from SQL Backup (3.2.0)
    SQL Backup 3.2.0, (c) Red Gate Software Ltd 2004 - 2005
    Serial number: xxxxxx
    Reading SQB file header of "D:\SQLBACKUP\Bigben\transactionlogs\processed\LOG_(local)_Bigben_20050701 030002.sqb"
    Backup group ID : 176426093
    File number : 1 of 1
    Backup type : 2 (Transaction log)
    Backup size : 867.625 MB
    Backup start : Friday, July 01, 2005 3:00:03 AM
    Backup end : Friday, July 01, 2005 3:00:39 AM
    Server name : xxxxx
    Instance : (local)
    Database name : Bigben
    First LSN : 535261000000211600001
    Last LSN : 535491000000482700001
    Checkpoint LSN : 535491000000102500001
    Differential base LSN : 535490000000102000003


    Output from SQL Backup (3.2.0)
    SQL Backup 3.2.0, (c) Red Gate Software Ltd 2004 - 2005
    Serial number: xxxx
    Reading SQB file header of "D:\SQLBACKUP\Bigben\transactionlogs\processed\LOG_(local)_Bigben_20050701 040003.sqb"
    Backup group ID : 176426093
    File number : 1 of 1
    Backup type : 2 (Transaction log)
    Backup size : 488.438 MB
    Backup start : Friday, July 01, 2005 4:00:03 AM
    Backup end : Friday, July 01, 2005 4:00:23 AM
    Server name : xxxxxx
    Instance : (local)
    Database name : Bigben
    First LSN : 535491000000482700001
    Last LSN : 535687000001570400001
    Checkpoint LSN : 535687000001561400001
    Differential base LSN : 535490000000102000003
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    No they shouldn't. The chances of two files having the same number is 2^31. The chances of two consecutive backup files having the same number is, well, ?. That is the cause of the error you encountered.

    I'll log this so that SQL Backup will recognize the different LSNs and restore each file individually.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
Sign In or Register to comment.