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


RESTORE detected an error on page (0:0)


RESTORE detected an error on page (0:0)

Author
Message
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40852 Visits: 14413
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
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97083 Visits: 38988
Have you run the RESTORE VERIFYONLY on the files on your SQL Server 2005 system?

When was the last time you ran checkdb?

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)
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40852 Visits: 14413
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)

Group: General Forum Members
Points: 230044 Visits: 46344
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, 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


Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40852 Visits: 14413
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
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40852 Visits: 14413
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
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97083 Visits: 38988
Try copying the file using Robocopy and see if it verifies okay on the destination server.

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)
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40852 Visits: 14413
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
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40852 Visits: 14413
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
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40852 Visits: 14413
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
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