Sql-server – SQL Server restore a backup results in an error

Not possible.

Here is an MSDN forum post that describes how to create scripts from the SQL 2005 database that you can run on a SQL 2000 server that re-create the database, including data.

Note that if all you have is a 2005 backup file, you'll have to restore it to a 2005 instance before you can create these scripts.

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!