Multiple file log restore fails on earliest log
ihfrick
Posts: 6
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
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
Comments
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.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
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
I'll log this so that SQL Backup will recognize the different LSNs and restore each file individually.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8