Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Ideas on a backup that "fails" but Verify says it is good Expand / Collapse
Author
Message
Posted Tuesday, June 11, 2013 7:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 6:32 AM
Points: 136, 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."


Post #1462072
Posted Tuesday, June 11, 2013 7:08 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:21 PM
Points: 20,676, Visits: 32,263
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.



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)
Post #1462083
Posted Tuesday, June 11, 2013 7:17 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 39,866, Visits: 36,206
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 2008, MVP
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

Post #1462098
Posted Tuesday, June 11, 2013 7:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 6:32 AM
Points: 136, 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.
Post #1462100
Posted Tuesday, June 11, 2013 7:22 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 39,866, Visits: 36,206
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 2008, MVP
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

Post #1462106
Posted Tuesday, June 11, 2013 7:24 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:21 PM
Points: 20,676, Visits: 32,263
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.



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)
Post #1462107
Posted Tuesday, June 11, 2013 7:28 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:08 AM
Points: 5,863, Visits: 12,941
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.



---------------------------------------------------------------------

Post #1462114
Posted Tuesday, June 11, 2013 8:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 6:32 AM
Points: 136, 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.
Post #1462194
Posted Tuesday, June 11, 2013 9:57 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 12:39 AM
Points: 3,105, Visits: 11,494
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.




Post #1462251
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse