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

RESTORE detected an error on page (0:0) Expand / Collapse
Author
Message
Posted Friday, April 6, 2012 12:50 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 7,140, Visits: 12,763
I am trying to restore a backup taken from SQL 2005 to SQL 2008 R2. The restore process has worked countless times before, but this morning I received the error below:

RESTORE detected an error on page (0:0) in database "database_name" as read from the backup set.
Error: 3183, Severity: 16, State: 1.


I tried a second backup file from the day before but received the same result.

I ran a RESTORE VERIFYONLY on both backup files through the 2008 R2 instance and after about 10 minutes (backup file is ~100GB) received the following errors from both:

Msg 3189, Level 16, State 1, Line 1
Damage to the backup set was detected.
Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.


CHECKDB ran clean last weekend and no I/O errors were found in the error log on 2005.

At present I am re-copying the latest backup file to the 2008 R2 server thinking it could have been a corruption when copying the file around the network...however I have little hope that is the issue since I get the same result from both backups taken on consecutive nights.

Any insight into what might be going on?


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1279635
Posted Friday, April 6, 2012 12:59 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 9:06 AM
Points: 20,858, Visits: 32,878
Have you run the RESTORE VERIFYONLY on the files on your SQL Server 2005 system?

When was the last time you ran checkdb?



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 #1279647
Posted Friday, April 6, 2012 2:16 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 7,140, Visits: 12,763
Lynn Pettis (4/6/2012)
Have you run the RESTORE VERIFYONLY on the files on your SQL Server 2005 system?

I am running the verify now...will post back when complete.

When was the last time you ran checkdb?

Checkdb ran clean Sunday night.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1279690
Posted Friday, April 6, 2012 2:33 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 40,615, Visits: 37,080
I can pretty much tell you that it is not a corrupt page in the source DB. There is no page 0:0 in a database, and if page 1:0 was damaged you'd know all about it (the database would be offline)

No 3rd party backup tools on the 2005 server? No hyperbak, litespeed, etc, etc, etc?



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 #1279697
Posted Friday, April 6, 2012 2:37 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 7,140, Visits: 12,763
GilaMonster (4/6/2012)
I can pretty much tell you that it is not a corrupt page in the source DB. There is no page 0:0 in a database, and if page 1:0 was damaged you'd know all about it (the database would be offline)

No 3rd party backup tools on the 2005 server? No hyperbak, litespeed, etc, etc, etc?

No 3rd party backup tools. This is a native full backup from a 2005 instance trying to restore to a 2008 R2 instance.

Now, in between the two instances the file copy is done by a block-level file replication software.

The verifyonly restore is still running on the 2005 instance.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1279698
Posted Friday, April 6, 2012 3:20 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 7,140, Visits: 12,763
The RESTORE VERIFYONLY returned clean:

The backup set on file 1 is valid.




__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1279715
Posted Friday, April 6, 2012 3:23 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 9:06 AM
Points: 20,858, Visits: 32,878
Try copying the file using Robocopy and see if it verifies okay on the destination server.



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 #1279718
Posted Friday, April 6, 2012 3:33 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 7,140, Visits: 12,763
Lynn Pettis (4/6/2012)
Try copying the file using Robocopy and see if it verifies okay on the destination server.

I wish I could...the 2005 and 2008 R2 data centers are geo-separated and a standard Windows copy takes 10+ hours. A potential problem with the block-level copy seems to be the most promising lead. Odd thing is every other backup that travels the same path is fine restoring to 2008 R2, and this only started happening with the one database this morning. Thanks for looking and for the info and suggestions. I'll post back if I get to the bottom of it, or not, to close the loop either way.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1279726
Posted Friday, April 6, 2012 4:17 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 7,140, Visits: 12,763
OK, I think I ruled out the block-level copy step as a possible problem. I just successfully restored the supposedly problematic-backup to a different 2008 R2 server in the remote data center.

One item of interest, in the error log I see hundreds of thousands of the same error message that returned when the backup fails:

RESTORE detected an error on page (0:0) in database "database_name" as read from the backup set.
Error: 3183, Severity: 16, State: 1.


Many thousands are entered with the same time stamp, and then many more thousands a second or two later.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1279743
Posted Thursday, May 10, 2012 10:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 7,140, Visits: 12,763
I ran into another issue Tuesday and finally nailed it down to a file-copy operation that occurs prior to restore, but after the the block-level copy operation. I am just happy the block-level copy operation proved to be solid in these two occurrences as that is where the heavy lifting is done in terms of moving these large files between two geo-separated data centers. I added a piece of code to the process that later copies the files around in the remote data center to check the MD5 hash of the file before and after the problematic file-copy operation and to send an email if they do not match.

So far I have not seen the issue again, but at least now I am confident I will be able to troubleshoot the issue fast if it happens in the future, hopefully before any users are affected. If this becomes a regular occurrence I am considering bringing ESEUtil into the mix. Per this article it seems to be quite reliable for copying large files over slow or poor network connections...much more so than what the script is doing, a CmdShell COPY operation:

How to: Copy very large files across a slow or unreliable network by Grant Holliday


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1298012
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse