Database stuck in restoring state

database sql-server-2008-r2 database-restore repair
Aaron Lewis picture Aaron Lewis · Dec 22, 2010 · Viewed 16.6k times · Source

Last night I had a database that had it's data and log files physically removed without first taking the proper steps to move the database files.

After the disk were physically reattached, the database went into "Restoring..." state.

I have tried the most recommended steps for this situation, including restoring with recovery, dropping the database, detaching the database, and taking the database offline. If I try to drop or delete the database, SSMS tells me that the command was successful, but when I refresh the connection, the database appears back in the db list with the state of Restoring...

I'm wondering if there are any other methods of repairing the database or even just removing it, without taking the server or service offline.

Edit:

The errors look like this:

Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. Msg 823, Level 24, State 2, Line 1 The operating system returned error 64(The specified network name is no longer available.) to SQL Server during a read at offset 0x00000000012000 in file '**'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Answer

Raymund picture Raymund · Dec 22, 2010

I had experienced a worse scenario and I had written up a guide which might help you out, I guess you can start on the part where I started restoring the database. Here is it http://anyrest.wordpress.com/2010/08/24/reparing-suspect-sql-database/

Related questions

How to solve SQL Server Error 1222 i.e Unlock a SQL Server table

I am working in a database where I load data in a raw table by a data loader. But today the data loader got stuck for unknown reasons. Then I stopped the data loader from windows task manager. But then …

Read More
What is the syntax meaning of RAISERROR()

I just created a Instead After Trigger whose syntax is given below: Create trigger tgrInsteadTrigger on copytableto Instead of Insert as Declare @store_name varchar(30); declare @sales int; declare @date datetime; select @store_name = i.store_name from inserted i …

Read More
Create a view with ORDER BY clause

I'm trying to create a view with an ORDER BY clause. I have create it successfully on SQL Server 2012 SP1, but when I try to re-create it on SQL Server 2008 R2, I get this error: Msg 102, Level 15, State 1, Procedure TopUsers, …

Read More