SQL Server 2005 Restore Backup Status Recovery Pending

So at work something dreadful happened a database server went down and I am having an issue recovering one of our databases from our backups. When I try to restore from a backup using SQL Management Studio the backup proceeds to 100% then fails at the end with the following:

An inconsistency was detected during an internal operation. Please contact technical support. Reference number 8

Error Number: 5243
Severity: 22
State: 1
Line Number: 1

There were some articles stating that a hotfix or servicepack update solves issues similar to this but it did not in my case. From what I've read, error 5243 and 5242 usually mean possible corruption. I ran:

RESTORE VERIFYONLY
FROM DISK=N'C:\DataBaseName.bak'
WITH CHECKSUM;

and it indicated that no checksums were in use so I then ran:

RESTORE VERIFYONLY
FROM DISK=N'C:\DataBaseName.bak'

and it completed successfully with:

Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.
Directory lookup for the file "M:\Data\DataBaseName.mdf" failed with the operating system error 3(The system cannot find the path specified.).
Directory lookup for the file "N:\Logs\DataBaseName_log.ldf" failed with the operating system error 3(The system cannot find the path specified.).
The backup set on file 1 is valid.

Sense no checksums are used this only checks the header, this I know. Now I try to force the restore to complete using CONTINUE_AFTER_ERROR:

RESTORE DATABASE DataBaseName
FROM DISK=N'C:\DataBaseName.bak'
WITH CONTINUE_AFTER_ERROR;

This completes successfully with:

```
Processed 1453208 pages for database 'DataBaseName', file 'DEFAULTDB' on file 1.
Processed 3 pages for database 'DataBaseName', file 'DEFAULTDB_log' on file 1.
Restore was successful but deferred transactions remain. These transactions can not be resolved because there are data that is unavailable. Either use RESTORE to make that data available or drop the filegro

You can restore the database files to other locations using T-SQL commands to do the restore.

I did a test, something like:

CREATE DATABASE TestDB
ON (FILENAME='C:\test\TestDB.mdf', NAME='TestDB')
LOG ON (FILENAME='C:\test\TestDBLog.ldf', NAME='TestLog');
GO

BACKUP DATABASE TestDB TO DISK='C:\Test\TestDB.BAK';
GO

DROP DATABASE TestDB;
GO

RESTORE DATABASE TestDB FROM DISK='C:\Test\TestDB.BAK'
WITH MOVE 'TestDB' TO 'C:\SomeOtherFolder\TestDB.mdf'
    , MOVE 'TestLog' TO 'C:\SomeOtherFolder\TestLog.ldf'
    , RECOVERY;

This creates a database in one folder, takes a backup of the database, drops the database, then restores it from backup into a new folder. You should be able to modify the RESTORE DATABASE command to suit your needs.

StackExchange Database Administrators Q#68507, answer score: 3