Restore succeeded but Data is different?? What? Is this possible?

Question

Restore succeeded but Data is different?? What? Is this possible?

DominantDBA

SSC Eights!

Points: 862
More actions
January 29, 2014 at 9:23 am

#287749

Hi All
Scenario:
A backup is taken and is 50gb uncompressed and is copied from one domain(A) into Domain(B). It is restored and all is well. The same backup file is then copied to Domain(C). DBCC CHeckDB is run on the database and comes back with a error that data in a row is different from whats in the clustered index
I check the row and there is indeed bad data in the row that makes no sense but seemingly only this row?
Question:
Is this possible? If so how? Also what are the best ways outside of striping and compression that can be used to lower the risk of this happening?
thanks
Chris

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

DominantDBA (1/29/2014)
Hi All
Scenario:
A backup is taken and is 50gb uncompressed and is copied from one domain(A) into Domain(B). It is restored and all is well. The same backup file is then copied to Domain(C). DBCC CHeckDB is run on the database and comes back with a error that data in a row is different from whats in the clustered index
I check the row and there is indeed bad data in the row that makes no sense but seemingly only this row?
Question:
Is this possible? If so how? Also what are the best ways outside of striping and compression that can be used to lower the risk of this happening?
thanks
Chris

Corrupt data is possible at any time in any database. If you backup said bad data you will restore said bad data (assuming the bad data doesn't cause a failure to restore, which would be exceptionally unlikely). This is the case whether or not compression is in play on the backup.

I also note that just writing out the backup itself could possibly INTRODUCE some data corruption if a bit(s) didn't actually write correctly and wasn't caught and corrected before the write was finalized. This is one case where perhaps compression holds the potential to introduce a corruption that wasn't there. But I feel that is extremely unlikely too.

I suppose it is also possible that when the database is RESTORED that write could also flip a bit(s) and introduce a data corruption. Similarly extremely unlikely and nothing you could do anyway about this one.

I would run a checkdb on the B machine. If you get the corruption there your PRIMARY database is almost certainly corrupt and you need to pursue that.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

DominantDBA SSC Eights! Points: 862 More actions · Answer 2

I ran a DBCC CHECKDB on B and its fine with different data(correct) in that row than on C. So Im happy with the data in the primary

The same backup was used for the restore but it SEEMS to have been corrupted on copy or at least that seems to be the only feasible however unlikely answer. Scary

I'm getting the backup file copied over again and will restore tomorrow which will tell us more

Thanks

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

DominantDBA (1/29/2014)
I ran a DBCC CHECKDB on B and its fine with different data(correct) in that row than on C. So Im happy with the data in the primary
The same backup was used for the restore but it SEEMS to have been corrupted on copy or at least that seems to be the only feasible however unlikely answer. Scary
I'm getting the backup file copied over again and will restore tomorrow which will tell us more
Thanks

Oh, sorry! I completely forgot to mention the obvious (and possibly most likely??) point that the network copy could have introduced a corruption! :hehe:

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

Either the backup was damaged in the copy and hadn't been taken WITH CHECKSUM or the IO subsystem corrupted the database during/after the 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

Nadrek SSC-Insane Points: 20039 More actions · Answer 5

DominantDBA (1/29/2014)
Hi All
Scenario:
A backup is taken and is 50gb uncompressed and is copied from one domain(A) into Domain(B). It is restored and all is well. The same backup file is then copied to Domain(C).

First test: Are the backup files on A, B, and C actually identical?

In order to avoid possible further network corruption, I'd generate hashes of all three files where they're sitting right now, and compare. If the hashes are different, the files are different.

Hashing software:

md5sum and sha1sum from GnuWin32 (native windows executables):

http://sourceforge.net/projects/getgnuwin32/[/url]

Java "Jacksum" (many, many hash algorithms):

http://www.jonelo.de/java/jacksum/[/url]

Microsoft FCIV (I've never used it): http://support.microsoft.com/kb/889768

DominantDBA SSC Eights! Points: 862 More actions · Answer 6

So the network copy from B to C was corrupted

Process has been changed and a CHECKSUM is being used in the BACKUP with compression and striping

thanks to all who posted

Nadrek SSC-Insane Points: 20039 More actions · Answer 7

Very interesting.

Essentially, you need a robust, verifiable network copy, then.

I'd suggest taking a hash of the actual backup file before the first copy, and then checking it after each copy - if it's different, then re-copy.

Alternately, if the corruption is minor, perhaps ECC would be better - for a 50GB file, you might be able to get away with par2 (http://chuchusoft.com/par2_tbb/[/url]). That way, if the ECC verify fails, then perhaps the ECC repair will succeed, and you won't have to recopy the file.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 8

DominantDBA (1/30/2014)
So the network copy from B to C was corrupted

Not necessarily. I gave two possibilities.

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 9

Nadrek (1/30/2014)
I'd suggest taking a hash of the actual backup file before the first copy, and then checking it after each copy - if it's different, then re-copy.

No need, if he's now doing backups with checksum. Can just restore verifyonly and SQL will recalc the checksum and throw an error if the backup file has changed since backup.

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

Nadrek SSC-Insane Points: 20039 More actions · Answer 10

GilaMonster (1/30/2014)
No need, if he's now doing backups with checksum. Can just restore verifyonly and SQL will recalc the checksum and throw an error if the backup file has changed since backup.

Agreed, if either SQL Server can restart the file copy, or if they handle the error in some other way they're happy with. I suggested the post-copy, pre-SQL check in order to make the automation more robust.