SQL Backup failure on verify step

tlovietlovie Posts: 6
edited November 26, 2010 10:17AM in SQL Backup Previous Versions
I recently tried to restore a backup of a database that was created with SQL backup and I was not successful. It's a development system, so it wasn't really important and I was able to recover by restoring from a different backup (MS format). This experience had me worried, since if I really needed to restore, I may not be able to, so I decided to turn on verify, to ensure (hopefully) that the backup set was readable.

I'm getting consistent failures on the verify step of certain databases. See log below.

SQL Backup log file 6.4.0.56

-SQL "BACKUP DATABASE [CFM] TO DISK = 'G:\SQL2008\MSSQL10.SQL2008\MSSQL\Backup\<AUTO>.sqb' WITH ERASEFILES = 1b, ERASEFILES_REMOTE = 1b, MAILTO_ONERROR = 'tom@exigentsi.com', DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, COMPRESSION = 4, COPYTO =
'\\192.168.6.7\scratch\CFMBackup', THREADCOUNT = 2, VERIFY "
ERRORS AND WARNINGS


11/9/2010 12:09:28 PM:
11/9/2010 12:09:28 PM: Verifying files:
11/9/2010 12:09:28 PM: G:\SQL2008\MSSQL10.SQL2008\MSSQL\Backup\FULL_SQL2008_CFM_20101109_120518.sqb
11/9/2010 12:09:28 PM:
11/9/2010 12:10:31 PM: Thread 0 error:
Failed to service command. Error code: -2139684860
Process terminated unexpectedly. Error code: -2139684860 (An abort request is preventing anything except termination actions.)
11/9/2010 12:10:31 PM:
11/9/2010 12:10:31 PM: Thread 1 error:
Process terminated unexpectedly. Error code: -2139684860 (An abort request is preventing anything except termination actions.)
11/9/2010 12:10:31 PM:
11/9/2010 12:10:31 PM: SQL error 3013: SQL error 3013: VERIFY DATABASE is terminating abnormally.
11/9/2010 12:10:31 PM: SQL error 3242: SQL error 3242: The file on device 'SQLBACKUP_6E34399D-7467-4E56-8D88-FB9BA5A2539B01' is not a valid Microsoft Tape Format backup set.
11/9/2010 12:10:31 PM: SQL error 3203: SQL error 3203: Read on "SQLBACKUP_6E34399D-7467-4E56-8D88-FB9BA5A2539B" failed: 995(The I/O operation has been aborted because of either a thread exit or an application request.)
11/9/2010 12:10:31 PM: Validation of all backup files failed.


PROCESSES COMPLETED SUCCESSFULLY

11/9/2010 12:05:18 PM: Backing up CFM (full database) on SQL2008 instance to:
11/9/2010 12:05:18 PM: G:\SQL2008\MSSQL10.SQL2008\MSSQL\Backup\FULL_SQL2008_CFM_20101109_120518.sqb

11/9/2010 12:05:18 PM: BACKUP DATABASE [CFM] TO VIRTUAL_DEVICE = 'SQLBACKUP_05CA444F-E4A4-4735-A1DD-1CA0AD4195AB', VIRTUAL_DEVICE = 'SQLBACKUP_05CA444F-E4A4-4735-A1DD-1CA0AD4195AB01' WITH BUFFERCOUNT = 12, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576,
NAME = N'Database (CFM), 11/9/2010 12:05:18 PM', DESCRIPTION = N'Backup on 11/9/2010 12:05:18 PM Server: BASHFUL\SQL2008 Database: CFM', FORMAT

11/9/2010 12:09:28 PM: Database size : 24.804 GB
11/9/2010 12:09:28 PM: Compressed data size: 1.117 GB
11/9/2010 12:09:28 PM: Compression rate : 95.50%

11/9/2010 12:09:28 PM: Processed 865048 pages for database 'CFM', file 'CFM_Data' on file 1.
11/9/2010 12:09:28 PM: Processed 141456 pages for database 'CFM', file 'CFM_Data02' on file 1.
11/9/2010 12:09:28 PM: Processed 2 pages for database 'CFM', file 'CFM_Log' on file 1.
11/9/2010 12:09:28 PM: Processed 0 pages for database 'CFM', file 'CFM_Log2' on file 1.
11/9/2010 12:09:28 PM: BACKUP DATABASE successfully processed 1006506 pages in 249.719 seconds (31.488 MB/sec).
11/9/2010 12:10:31 PM: Mail sent successfully to: tom@exigentsi.com
· Share on Twitter

Comments

  • tlovietlovie Posts: 6
    I've done some more testing on this, and when I change the compression level to 1, it starts working. It also fails with a different error on compression level 3.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    Would you be able to send us one of the backup files that could not be restored?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • tlovietlovie Posts: 6
    How would I do that... they are ~ 2GB. I don't think I could email it.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    We can set up a private FTP account for you on the Red Gate site where you can upload the file.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • tlovietlovie Posts: 6
    I'm still getting errors for certain backup jobs.

    -SQL "BACKUP DATABASE [CFM] FILEGROUP = 'PRIMARY' TO DISK = 'G:\SQL2008\MSSQL10.SQL2008\MSSQL\Backup\<AUTO>.sqb' WITH ERASEFILES = 1b, ERASEFILES_REMOTE = 1b, MAILTO_ONERROR = 'tom@exigentsi.com', DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, COPYTO = '\\192.168.6.7\CFMBackup', THREADCOUNT = 3, VERIFY "
    ERRORS AND WARNINGS


    11/22/2010 10:30:32 AM:
    11/22/2010 10:30:32 AM: Verifying files:
    11/22/2010 10:30:32 AM: G:\SQL2008\MSSQL10.SQL2008\MSSQL\Backup\FILE_SQL2008_CFM_20101122_102925.sqb
    11/22/2010 10:30:32 AM:
    11/22/2010 10:30:54 AM: SQL Server error 11/22/2010 10:30:54 AM:
    11/22/2010 10:30:54 AM: SQL error 3013: SQL error 3013: VERIFY DATABASE is terminating abnormally.
    11/22/2010 10:30:54 AM: SQL error 3189: SQL error 3189: Damage to the backup set was detected.
    11/22/2010 10:30:54 AM: Validation of all backup files failed.


    PROCESSES COMPLETED SUCCESSFULLY

    11/22/2010 10:29:25 AM: Backing up CFM (files/filegroups) on SQL2008 instance to:
    11/22/2010 10:29:25 AM: G:\SQL2008\MSSQL10.SQL2008\MSSQL\Backup\FILE_SQL2008_CFM_20101122_102925.sqb

    11/22/2010 10:29:25 AM: BACKUP DATABASE [CFM] FILEGROUP = 'PRIMARY' TO VIRTUAL_DEVICE = 'SQLBACKUP_5A317C47-7287-4458-A923-B206CEB0BB59', VIRTUAL_DEVICE = 'SQLBACKUP_5A317C47-7287-4458-A923-B206CEB0BB5901', VIRTUAL_DEVICE = 'SQLBACKUP_5A317C47-7287-4458-A923-B206CEB0BB5902' WITH BUFFERCOUNT = 18, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (CFM), 11/22/2010 10:29:25 AM', DESCRIPTION = N'Backup on 11/22/2010 10:29:25 AM Server: BASHFUL\SQL2008 Database: CFM', FORMAT

    11/22/2010 10:30:31 AM: Backup data size : 7.584 GB
    11/22/2010 10:30:31 AM: Compressed data size: 1.991 GB
    11/22/2010 10:30:31 AM: Compression rate : 73.75%

    11/22/2010 10:30:31 AM: Processed 990496 pages for database 'CFM', file 'CFM_Data' on file 1.
    11/22/2010 10:30:31 AM: Processed 0 pages for database 'CFM', file 'CFM_Log' on file 1.
    11/22/2010 10:30:31 AM: Processed 0 pages for database 'CFM', file 'CFM_Log2' on file 1.
    11/22/2010 10:30:31 AM: Processed 1 pages for database 'CFM', file 'CFM_Log3' on file 1.
    11/22/2010 10:30:31 AM: BACKUP DATABASE...FILE=<name> successfully processed 990497 pages in 59.262 seconds (130.576 MB/sec).
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    Can you send us the file is we set up an FTP account for you?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • tlovietlovie Posts: 6
    I think I understand why it was failing on these databases.... dbcc checkdb was not coming back clean. Once I rectified that, the backups are fine.
    · Share on Twitter
Sign In or Register to comment.