Log Backup report success but file is corrupt

BaldCoder

Hi, I'm running : SQL Backup Verison 5.2.0.2825 and I have the following problem. I have a log-shipping pair set up, periodically the Log Backup fails but writes an SQB file anyway. Usually it does not perform the COPYTO the remote server. The SQL Backup log on the source DB INCORRECTLY reports success:

SQL Backup log file 5.2.0.2825
5/12/2008 3:00:01 AM: Backing up <<MYDBNAME>> (transaction log) to:
5/12/2008 3:00:01 AM: \<<MYDBNAME>>_Backup\LOG\LOG_<<MYDBNAME>>_20080512030001.sqb

5/12/2008 3:00:01 AM: BACKUP LOG [<<MYDBNAME>>] TO DISK = 'D:\<<MYDBNAME>>_Backup\LOG\<TYPE>_<DATABASE>_<DATETIME YYYYmmddhhnnss>.sqb' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', VERIFY, MAILTO_ONERROR = 'dba@<<mycompanyname>>.com', COPYTO = '\\<<MYREMOTESERVER>>\Remote_Backups\<<MYDBNAME>>\LOGSHIPTO', COMPRESSION = 1

5/12/2008 3:00:26 AM: Thread 0 error:
Error 620: Error writing to backup file(s).
5/12/2008 3:00:26 AM: Warning 210: Thread 0 warning:
Warning 210: Error writing to backup file: \<<MYDBNAME>>_Backup\LOG\LOG_<<MYDBNAME>>_20080512030001.sqb
Warning: System error 87 (The parameter is incorrect)
Processed 1504 pages for database '<<MYDBNAME>>', file '<<MYDBNAME>>_Log' on file 1.
BACKUP LOG successfully processed 1504 pages in 0.580 seconds (21.240 MB/sec).
5/12/2008 3:00:28 AM: Mail sent successfully to: dba@<<mycompanyname>>.com



If I copy the file manually, the remote server fails the Log Restore, typically reporting in the SQL Backup log:

SQL Backup log file 5.2.0.2825
5/12/2008 11:24:15 AM: Restoring <<MYDBNAME>> (transaction logs) from:
5/12/2008 11:24:15 AM: C:\Remote_Backups\<<MYDBNAME>>\LOGSHIPTO\LOG_<<MYDBNAME>>_20080512030001.sqb

5/12/2008 11:24:15 AM: RESTORE LOG [<<MYDBNAME>>] FROM DISK = 'C:\Remote_Backups\<<MYDBNAME>>\LOGSHIPTO\LOG_<<MYDBNAME>>_20080512030001.sqb' WITH STANDBY = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Undo_<<MYDBNAME>>.dat'

5/12/2008 11:24:16 AM: Thread 0 error:
File read error for 345 bytes (balance 114 bytes). Backup file is incomplete or corrupted.

SQL error 3013: SQL error 3013: RESTORE LOG is terminating abnormally.
SQL error 3242: SQL error 3242: The file on device 'SQLBACKUP_8E0E0FF6-8800-4721-8E41-980294CC0E77' is not a valid Microsoft Tape Format backup set.


If I use the SQL Backup GUI to restore the file, it shows the file has unknown LSNs and then fails with the same message if you continue.

I have specified the VERIFY option on the Log Backup but this doesn't solve the problem.

I have also tried the SQL2MTF utility but this also fails reporting that the Backup file is incomplete or corrupt.

To correct the problem, necessitates a Full Backup and Restore cycle before restarting the Log-ship pair.

This problem occurs every couple of weeks. Please can anyone cast any light on why SQL Backup fails but continues to write the SQB, and then continues creating new Log Backups as if the error never occurred?

Thanks.

petey

This is a known bug in version 5.2 and below, where the last block of compressed data is not always correctly written to disk with the right parameters. I would suggest you upgrade to version 5.3.

SQL Backup is reporting an error with the write (Warning: System error 87 (The parameter is incorrect)). SQL Server is reporting statistics of the backup, because it has passed all the backup data to SQL Backup. The COPYTO didn't run because SQL Backup recognises that the backup file is unusable.