RESTORE detected an error on page (0:0)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Data Corruption » RESTORE detected an error on page (0:0)

RESTORE detected an error on page (0:0)

Rate Topic

Display Mode

Topic Options

Author

Message

opc.three

Posted Friday, April 06, 2012 12:50 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:24 PM
Points: 6,826, Visits: 11,950

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1279635

Lynn Pettis

Posted Friday, April 06, 2012 12:59 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 21,832, Visits: 27,858

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)

Post #1279647

opc.three

Posted Friday, April 06, 2012 2:16 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:24 PM
Points: 6,826, Visits: 11,950

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1279690

GilaMonster

Posted Friday, April 06, 2012 2:33 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 38,089, Visits: 30,383

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

opc.three

Posted Friday, April 06, 2012 2:37 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:24 PM
Points: 6,826, Visits: 11,950

GilaMonster (4/6/2012)

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1279698

opc.three

Posted Friday, April 06, 2012 3:20 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:24 PM
Points: 6,826, Visits: 11,950

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1279715

Lynn Pettis

Posted Friday, April 06, 2012 3:23 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 21,832, Visits: 27,858

Try copying the file using Robocopy and see if it verifies okay on the destination server.

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1279718

opc.three

Posted Friday, April 06, 2012 3:33 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:24 PM
Points: 6,826, Visits: 11,950

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1279726

opc.three

Posted Friday, April 06, 2012 4:17 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:24 PM
Points: 6,826, Visits: 11,950

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1279743

opc.three

Posted Thursday, May 10, 2012 10:02 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:24 PM
Points: 6,826, Visits: 11,950

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1298012

« Prev Topic | Next Topic »

Permissions