SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Ideas on a backup that "fails" but Verify says it is good


Ideas on a backup that "fails" but Verify says it is good

Author
Message
Ellen-477471
Ellen-477471
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 314
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.



Date 6/10/2013 11:18:23 PM
Log Job History (DBNAME_DAILY_DIFFERENTIAL)

Step ID 2
Server TESTSRVR
Job Name DBNAME_DAILY_DIFFERENTIAL
Step Name Run differential backup
Duration 00:02:24
Sql Severity 16
Sql Message ID 3013

Retries Attempted 0

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."
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39111 Visits: 38518
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86836 Visits: 45257
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Ellen-477471
Ellen-477471
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 314
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86836 Visits: 45257
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39111 Visits: 38518
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10280 Visits: 13687
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.

---------------------------------------------------------------------
Ellen-477471
Ellen-477471
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 314
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.
Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5706 Visits: 11771
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search