Restoring corrupt SQL Server 2005 database

Question

I am restoring a SQL Server 2005 database onto a SQL Server 2008 R2 instance using this command:

RESTORE DATABASE SampleDB FROM DISK= 'C:\SampleProject\SampleDB.bak' WITH REPLACE,
MOVE 'sampledb' TO 
'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SampleDB.mdf', 
MOVE 'sampledb_log' 
TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SampleDB_log.ldf',
CONTINUE_AFTER_ERROR, STATS = 10;

And the output is:

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Msg 3242, Level 16, State 2, Line 3
The file on device 'C:\SampleProject\SampleDB.bak' is not a valid Microsoft Tape Format backup set.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.

Why does the processed percent reach 100% but then terminate abnormally?

How can I restore this database backup even with corrupt data? Continue-after-error does not appear to make any difference.

Answer 1

Restoring a backup runs in three phases.

1. creating the files and zeroing them. (Log files are always zeroed, data files depend on a server setting)
2. reading the pages and writing them to their place in the new files
3. rolling forward (committed transactions that did not hit a checkpoint yet) and then back (uncommitted transactions).

Only phase 2 is measured by the percent counter. If you are restoring a very large log backup, the 3rd phase can actually take significantly longer than the 2nd phase all the while the percent done counter is sitting at 100%.

Every full database backup always contains a portion of the log. If you have logical inconsistencies within that piece of the backup file, SQL Server would not find out until it is in the 3rd phase. That's why you are seeing 100%.

---

The best resource for dealing with SQL Server corruption is sqlskills.com. More likely then not however you will find that you can't get to the data with build-in methods. If the problem lies indeed in the log section, you might be able to use an external vendor tool that can read backups like red-gate SQL data compare. There are also tools out there that can help with data recovery from corrupt databases like apexsql recover, but I am not sure if any of those can actually read a backup.

Answer 2

Like Sebastian said, ApexSQL Recover can be used to recover from corruption, but it doesn't seem to be a right solution in this scenario. The option to recover from a corrupted database or an MDF file reads only the online databases and detached MDF files. It doesn't read corrupted database backups

There is another option in ApexSQL Recover that reads corrupted backups, that's Recover table data from a database backup. However, note that it recovers only table data and creates an INSERT script, so it cannot be used to move the whole database to a newer SQL Server version

There are other ApexSQL tools that can help, as they read database backups - ApexSQL Diff and ApexSQL Data Diff. As the backup is not severely corrupted, they might be able to read it. Both tools read both online databases and database backups, they can also be used if you have an online SQL Server 2005 database that you want to upgrade to SQL Server 2008 R2

ApexSQL Diff is a SQL Server database comparison and synchronization tool which detects differences between database objects in live and versioned databases, backups, snapshots, and script folders. Comparing your live SQL Server 2005 database or database backup to a blank SQL Server 2008 destination creates the SQL objects that exist in the source (i.e. SQL Server 2005 database)

Once you recover the database structure, use ApexSQL Data Diff to recover data

You can find the recommended steps here:

Migrate a SQL Server database to a newer version of SQL Server

Create a database script from a backup without restoring it

Disclaimer: I work for ApexSQL as a Support Engineer