Error on restore of Transaction Log

SamCSamC Posts: 28
edited February 21, 2006 10:22AM in SQL Backup Previous Versions
After restoring multiple transaction log files successfully, SQL Backup terminated with "Restore Failed" with the message "Log file is not a valid tape backup".

It is only then that I realized the individual Log filenames are not listed in the restore log.
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    For the log file that failed to restore, could you pls try using sqb2mtf to convert it, then restore it using the regular T-SQL RESTORE command?

    If you restore multiple log files, the log file does contain the individual log file names that were restored e.g.

    5/5/2005 12:16:56 PM: Restoring transaction logs for database logtest from "c:\@backups\*.sqb"
    5/5/2005 12:16:56 PM:
    5/5/2005 12:16:56 PM: Processing log group 224409789 ...
    5/5/2005 12:16:56 PM: c:\@backups\logtest_log_01.sqb
    5/5/2005 12:16:56 PM: RESTORE LOG [logtest] FROM DISK = 'c:\@backups\logtest_log_01.sqb' WITH NORECOVERY
    5/5/2005 12:16:56 PM:
    5/5/2005 12:16:56 PM:
    5/5/2005 12:16:56 PM: Completed.
    5/5/2005 12:16:56 PM:
    5/5/2005 12:16:57 PM: Processing log group 224442557 ...
    5/5/2005 12:16:57 PM: c:\@backups\logtest_log_02.sqb
    5/5/2005 12:16:57 PM: RESTORE LOG [logtest] FROM DISK = 'c:\@backups\logtest_log_02.sqb' WITH NORECOVERY
    5/5/2005 12:16:57 PM:
    5/5/2005 12:16:57 PM:
    5/5/2005 12:16:58 PM: Completed.
    5/5/2005 12:16:58 PM:
    5/5/2005 12:16:59 PM: Processing log group 224495805 ...
    5/5/2005 12:16:59 PM: c:\@backups\logtest_log_03.sqb
    5/5/2005 12:16:59 PM: RESTORE LOG [logtest] FROM DISK = 'c:\@backups\logtest_log_03.sqb' WITH RECOVERY
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • SamCSamC Posts: 28
    I don't know which log file failed to restore. That's part of the problem with the SQL Backup Log output: 
    ----- SQL Server messages -----
Processed 2 pages for database 'nimc', file 'nimc_Log' on file 1.
RESTORE LOG successfully processed 2 pages in 0.002 seconds (7.168 MB/sec).

5/4/2005 8:18:22 AM: Restoring file: D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\nimc\LOG_(local)_nimc_20050504 054500.sqb
5/4/2005 8:18:22 AM: Restore started ...

----- SQL Server messages -----
Msg 3242, Level 16, State 1, Server VAIO3, Line 1
The file on device 'SQLBACKUP_8510493' is not a valid Microsoft Tape Format backup set.
Msg 3013, Level 16, State 1, Server VAIO3, Line 1
RESTORE LOG is terminating abnormally.
    There's no indication of which file was at fault.

    I'll try to locate the file today by restoring the transaction logs individually. Assuming the logs are processed individually, it's number 16 out of 22 transaction logs.
    · Share on Twitter
  • SamCSamC Posts: 28
    I re-ran the restore again. It seems to fail on the same transaction log.

    5/4/2005 8:18:22 AM: Restoring file: D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\nimc\LOG_(local)_nimc_20050504 054500.sqb

    There's no proprietary data in this transaction log. I can provide a zipped copy (5Kb) for your review.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    You're right, the GUI is not informative enough when an error happens during the multiple log restore. If you use the cmd line, it will show you which file(s) failed to restore.

    Try converting the file to a regular MTF file using sqb2mtf, and restore using that.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • SamCSamC Posts: 28
    The converted transaction log file restores properly with EM.
    · Share on Twitter
  • SamCSamC Posts: 28
    Brian,

    Wanted to let you know that I unzipped and installed the SQL Backup update.

    Unfortunately the problem doesn't seem to be corrected with this update.

    Sam
    · Share on Twitter
  • jgoemaatjgoemaat Posts: 9 Bronze 2
    I have received that same error very sporadically over the past months - usually once every month or so ... doing another backup and restore works fine. Is this basically some sort of corruption in the SQB file?

    It's a big pain because this leaves my database in "Loading" and sometimes "Loading/Suspect" status, which I have not been able to get the database out of, without re-creating.

    Is there a backup verify command I can at least run before I retore a bad full backup, so my database isn't left in non-recoverable "Loading" state?

    Thanks!
    2/21/2006 6:35:02 AM: SQL Backup 3.1.0, (c) Red Gate Software Ltd 2004 - 2005 
2/21/2006 6:35:02 AM: 
2/21/2006 6:35:02 AM: Restoring database Tango_Reporting from "H:\backup\TangoPart1.sqb"
2/21/2006 6:35:02 AM: 
2/21/2006 6:35:02 AM: RESTORE DATABASE [Tango_Reporting] FROM DISK = 'H:\backup\TangoPart1.sqb', DISK = 'H:\backup\TangoPart2.sqb'  WITH  RECOVERY, MOVE 'Tango_Data' TO 'F:\SQLDATA\Tango_Reporting_Data.mdf', MOVE 'Tango_Log' TO 'F:\SQLDATA\Tango_Reporting_Log.ldf', REPLACE, LOGTO = 'C:\Apps\SQLBackup', PASSWORD = 'XXXXXXXXXX'
2/21/2006 6:35:02 AM: 
2/21/2006 6:35:02 AM: osql -S TANGO2 -Q "RESTORE DATABASE [Tango_Reporting] FROM VIRTUAL_DEVICE = 'SQLBACKUP_151322461', VIRTUAL_DEVICE = 'SQLBACKUP_15132246101' WITH BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576 , RECOVERY, MOVE 'Tango_Data' TO 'F:\SQLDATA\Tango_Reporting_Data.mdf', MOVE 'Tango_Log' TO 'F:\SQLDATA\Tango_Reporting_Log.ldf', REPLACE" -b -w 120 -U sa -P XXXXXXXXXX

Msg 3242, Level 16, State 2, Server TANGO2, Line 1
The file on device 'SQLBACKUP_15132246101' is not a valid Microsoft Tape Format backup set.
Msg 3013, Level 16, State 1, Server TANGO2, Line 1
RESTORE DATABASE is terminating abnormally.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    The version of SQL Backup you are using is outdated. You should be using at least version 3.2.
    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.