3

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!

2
  • A question: which service pack is installed on your SQL Server 2005, if there is installed one?
    – splattne
    Jun 25, 2009 at 15:15
  • Its SQL 2005. No Service Pack. I was about to install Service Pack 1 Jun 25, 2009 at 16:03

1 Answer 1

11

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!

5
  • +1 Thanks, Paul! I'm actively reviewing our backup jobs now...
    – squillman
    Jun 25, 2009 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? Jun 25, 2009 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? Jun 25, 2009 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. Jun 25, 2009 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. Jun 25, 2009 at 16:21

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .