4

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.

  • How about RESTORE DATABASE DatabaseName WITH RECOVERY;? – Aaron Bertrand Jun 18 '14 at 17:39
  • 1
    This sounds like perhaps the M: drive doesn't exist? When you do a restore the server expects to store the files in the backup file to the original locations. Check RESTORE DATABASE in books online for ways to move the files to different locations. – Max Vernon Jun 18 '14 at 17:42
  • It sounds like this is a backup of just the primary file group and you are missing another filegroup. How many filegroups should this database have? – mrdenny Jun 18 '14 at 17:43
  • @MaxVernon you are right...there are mdf files in M drive and log files in N. I am a software developer and am not terribly familiar with database errors. I figured I would be able to restore to a local sql server 2005 instance on my development computer. Guess I need to be restoring to an sql server that has access to those network drives? – waltmagic Jun 18 '14 at 19:08
  • I went back to the restored sql server with the M drive and N drive and RESTORE VERIFYONLY FROM DISK=N'C:\DataBaseName.bak' completed and only mentioned, The backup set on file 1 is valid. Followed the same steps and the result is still the same, state is in "Pending Restore" – waltmagic Jun 18 '14 at 23:50
2

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.

  • I'm sorry my friend but this did not help me out. I appreciate you showing me how to move the mdf and ldf though :) – waltmagic Jun 18 '14 at 23:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.