Log Backup report success but file is corrupt
BaldCoder
Posts: 7
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.
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.sqb5/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.sqbWarning: 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.
Comments
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.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8