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
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.