SQL Server 2005 Restore Backup Status Recovery Pending

Question

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 filegroups if you never need this data again. Dropping the filegroup results in a defunct filegroup.
RESTORE WITH CONTINUE_AFTER_ERROR was successful but some damage was encountered. Inconsistencies in the database are possible.
RESTORE DATABASE successfully processed 1453211 pages in 457.277 seconds (26.033 MB/sec).

Here is where things get frustrating :) At this point the status of the database is "Recovery Pending". I have checked this by using this command:

SELECT
    [state_desc]
FROM
    sys.databases
WHERE
    [name] = N'DataBaseName';

I can take the database off line using

ALTER DATABASE DataBaseName SET OFFLINE;

and the status shows offline. I then try bringing it back online with

ALTER DATABASE DataBaseName SET ONLINE;

and it fails with:

Msg 945, Level 14, State 2, Line 1
Database 'DataBaseName' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5243, Level 22, State 1, Line 1
An inconsistency was detected during an internal operation. Please contact technical support. Reference number 8.

but when I check the state it is in "Pending Restore"

I then issue

ALTER DATABASE DataBaseName SET SINGLE_USER;

and this completes sucessfully. I then issue

ALTER DATABASE DataBaseName SET EMERGENCY;

and this completes sucessfully...good right? I then try

USE DataBaseName

and recieve, Msg 922, Level 14, State 1, Line 1 Database 'DataBaseName' is being recovered. Waiting until recovery is finished.

I am pretty much stuck here. If I run

DBCC CHECKDB (DataBaseTable, REPAIR_ALLOW_DATA_LOSS)

and still recieve:

Msg 922, Level 14, State 1, Line 1
Database 'DataBaseName' is being recovered. Waiting until recovery is finished.

I can't seem to get any further than this. Can anyone give me their two cents? Thanks a ton! I definitely know I am going to learn something here.

Answer 1

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.