Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have .bak files for the database of a project my company recently took over.

I was able to restore several of the .bak files, but the most important one is failing.

When it fails I simply get a generic error, and asked to check the log.

This is the error that occurs: Message SQL Server Assertion: File: , line=1443 Failed Assertion = 'pFile'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.

I don't know how to run DBCC CHECKDB on a *.bak file.

Any help would be greatly appreciated. While I have experience with SQL as a developer, I'm definitely not a DBA. So assume I'm an idiot. :)

Thanks!

share|improve this question
    
A question: which service pack is installed on your SQL Server 2005, if there is installed one? –  splattne Jun 25 '09 at 15:15
    
Its SQL 2005. No Service Pack. I was about to install Service Pack 1 –  Jack Marchetti Jun 25 '09 at 16:03

1 Answer 1

up vote 11 down vote accepted

That assertion happens because the restore code read a page from the backup but the page is corrupt and the file ID stamped in the page header doesn't exist in the database being restored. It's firing from a code file called bckioreq.cpp (I used to own all this stuff while at MS).

The message to run DBCC CHECKDB is a generic message that doesn't apply in this case.

I take it you're restoring a full database backup and then a series of other differential and/or log backups? You're restoring on 2005, but are you restoring an older backup?

This is what's called a retail-assert in the code - there's absolutely no way to get around it - as soon as the code hits it, the assertion will fire and the restore bombs out. There's a Connect item to make this nicer but it's not fixed in 2008 either.

Is this happening on the full backup you're restoring or one of the subsequent differential and/or log backups? If the full backup, there's nothing you can do - that backup is toast. If one of the later backups, you can restore everything up to but not including that backup.

That's basically your answer I'm afraid.

Now, how did this happen? (rhetorical question) It could be that the database that was backed up was corrupt, or that the I/O subsystem corrupted the backup. Couple of things you can do to help protect against this - turn on page checksums in the database and use the WITH CHECKSUM option on your backups. This adds some checking to make sure what's being backed up isn't corrupt. You can also validate your backups in various ways - checkout my blog post on this: Importance of validating backups.

Hope this helps!

share|improve this answer
    
+1 Thanks, Paul! I'm actively reviewing our backup jobs now... –  squillman Jun 25 '09 at 15:23
    
So basically what you're saying is I have to go back to the Firm that created the backup and say, do it again because this is corrupt? –  Jack Marchetti Jun 25 '09 at 16:02
    
And yeah I'm assuming it's the full backup. They sent along a translog as well, but i'm guessing its the full backup. Also, when I attempted to Restore using Mangement Studio, the database name was listed as "INCOMPLETE". Should that have been my first sign that something wasn't right? –  Jack Marchetti Jun 25 '09 at 16:10
    
Also, how would i be able to tell if it was a full backup? It's 8 gigs, so I'm guessing it is. –  Jack Marchetti Jun 25 '09 at 16:17
1  
Yup - you need another backup - and get them to consistency check it before taking the backup. To tell what kind of backup it is, do a RESTORE HEADERONLY on it and look at the BackupType column. 1 = full database backup, 2 = log, 5 = differential database. Hmm - yeah, the SSMS clue doesn't look good. Try using raw T-SQL rather than SSMS so you can control what's going on. –  Paul Randal Jun 25 '09 at 16:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.