April 6, 2012 at 12:50 pm
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
April 6, 2012 at 12:59 pm
Have you run the RESTORE VERIFYONLY on the files on your SQL Server 2005 system?
When was the last time you ran checkdb?
April 6, 2012 at 2:16 pm
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
April 6, 2012 at 2:33 pm
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
April 6, 2012 at 2:37 pm
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
April 6, 2012 at 3:20 pm
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
April 6, 2012 at 3:23 pm
Try copying the file using Robocopy and see if it verifies okay on the destination server.
April 6, 2012 at 3:33 pm
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
April 6, 2012 at 4:17 pm
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
May 10, 2012 at 10:02 am
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply