June 11, 2013 at 7:00 am
Lately on some of our backups the job will return a message that the backup failed but when I check to rerun the job there will exist a backup file [or files] and when I run the backup "Restore" statements against the file it says the backup is good.
Below is the log from the latest backup failure and the return statement from the verification.
I am curious if others have had this issue and if I should trust the backup or not.
Thank you for your comments.
Date6/10/2013 11:18:23 PM
LogJob History (DBNAME_DAILY_DIFFERENTIAL)
Step ID2
ServerTESTSRVR
Job NameDBNAME_DAILY_DIFFERENTIAL
Step NameRun differential backup
Duration00:02:24
Sql Severity16
Sql Message ID3013
Retries Attempted0
Message
Executed as user: DOMAINNAME\DBA. ... on file 1. [SQLSTATE 01000] (Message 4035) Processed 824 pages for database 'DBNAME', file 'DBNAME2' on file 1. [SQLSTATE 01000] (Message 4035) Processed 864 pages for database 'DBNAME', file 'DBNAME3' on file 1. [SQLSTATE 01000] (Message 4035) Processed 704 pages for database 'DBNAME', file 'DBNAME4' on file 1. [SQLSTATE 01000] (Message 4035) Processed 40 pages for database 'DBNAME', file 'DBNAME6' on file 1. [SQLSTATE 01000] (Message 4035) Processed 168 pages for database 'DBNAME', file 'DBNAME5' on file 1. [SQLSTATE 01000] (Message 4035) Processed 32 pages for database 'DBNAME', file 'DBNAME7' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1 pages for database 'DBNAME', file 'DBNAME_Log' on file 1. [SQLSTATE 01000] (Message 4035) A nonrecoverable I/O error occurred on file "\\VMBKUP01\SQL_BACKUPS\TESTSRVR\DBNAME\WK24-Mon_DBNAME_DIFF.BAK:" 64(The specified network name is no longer available.). [SQLSTATE 42000] (Error 3271) BACKUP DATABASE i... The step failed.
SO, it says it failed ... but, the file is there and when 'Verify' and 'Restore HeaderONLY', and 'Restore Filelistonly' are run against the backup all responses are good.
"The backup set on file 1 is valid."
June 11, 2013 at 7:08 am
Have you tried using the file to restore the database to test or development server to verify that the backup file is good?
That is the real test.
June 11, 2013 at 7:17 am
Ellen-477471 (6/11/2013)
Lately on some of our backups the job will return a message that the backup failed but when I check to rerun the job there will exist a backup file [or files] and when I run the backup "Restore" statements against the file it says the backup is good.Below is the log from the latest backup failure and the return statement from the verification.
I am curious if others have had this issue and if I should trust the backup or not.
The only thing that verify checks (if you didn;t do the backup with checksum) is the backup header and some metadata and a bit of the start of the backup. A backup can verify successfully and fail to restore.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 11, 2013 at 7:19 am
With this one I have not yet tried using it in a restore. The full backup is quite large and the test environment is not ready for a change [testing is in progress on other development]
In the past with a different database I did successfully , multiple times, use a full backup and differential to restore a database from a backup that had supposedly failed.
It leads me to distrust the "verify" and the backup messages.
But I don't trust assuming that a "failed" backup actually will be good when it is really needed.
I am interested to see if there are known bugs in the statements used to verify and identify backup files.
June 11, 2013 at 7:22 am
Ellen-477471 (6/11/2013)
I am interested to see if there are known bugs in the statements used to verify and identify backup files.
No bugs. But what verify does is not what most people assume it does. It is not a guarantee that the backup is restorable.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 11, 2013 at 7:25 am
Tell us more about the backup process. Are you using a third party backup solution or native backups. Are you backing up locally or directly to a network share. What type of device are you backing up to if not local.
June 11, 2013 at 7:28 am
are you backing up across the network? Looks like a network glitch to me (error 64), it says it in the error message.
I wouldn't trust the backup without a test restore somewhere.
---------------------------------------------------------------------
June 11, 2013 at 8:52 am
Gail:
Thank you, that makes the status returned by "Verify" make sense.
The first part of the backup is valid ... so I will trust that it is a true failure and run another one.
We have had trouble with our storage lately so the disappearance of the network path is a sympton of that problem.
Until we get that fixed I need to be vigilant that the backups complete 100% successful or rerun.
June 11, 2013 at 9:57 am
I suggest adding the CHECKSUM option to your backup command. Example:
backup database [MyDatabase]
to
disk = '\\MyBackupServer\MyBackupShare\MyDatabase_Backup.bak'
with
init,
compression,
checksum,
stats = 10;
CHECKSUM specifies that the backup operation will verify each page for checksum and torn page, if enabled and available, and generate a checksum for the entire backup.
RESTORE VERIFYONLY will check the checksum if it is present, so that enhances the ability of check to see if the backup set ifs valid, and is much more likely to spot a bad backup.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply