MSSQL database on external hard drive shows Recovery Pending

Question

I have created a database in SQL Server 2012 with mdf and ldf pointing to a external hard drive attached to my machine. I created tables, stored procedures, populated tables, etc. etc. I removed the hard drive at the end of the day.

Today, when I attached the hard drive and tried to access the DB in Management Studio, I see the name of the database with (Recovery Pending).

What does this mean? I see the mdf and ldf files in the D drive.

SQL Server is not Access. Severing the data and log files from it is a "bad thing", even if you're the only one in the database — billinkc, Sep 15 '14 at 18:27
@bluepiranha that was a bad thing to do. You should not do bad things like this. — Zane, Sep 15 '14 at 18:33

score 12 · Answer 1 · edited Feb 12 '15 at 09:39

12

What worked for me was to take the database offline*, then back online - no RESTORE DATABASE was necessary in this case, so far as I can tell.

In SQL Server Management Studio:

right-click on the database
select Tasks / Take Offline ... breathe deeply, cross fingers...
right-click on the database again
select Tasks / Take Online

edited Feb 12 '15 at 09:39

Matt Clark

27,671
19
68
123

answered Feb 11 '15 at 08:00

Jonathan Griffiths

121
1
3

Perfect!! Although, for me, I had to close and reopen SSMS afterwords to get the database objects to repopulate. – Jim Simson Nov 11 '18 at 14:00

score 9 · Accepted Answer · answered Sep 15 '14 at 18:38

When you removed the drive, you forcefully disconnected the database from the SQL Server service. SQL Server does not like that.

SQL Server is designed by default so that any database created is automatically kept open until either the computer shuts down, or the SQL Server service is stopped. Prior to removing the drive, you should have "Detached" the database, or stopped the SQL Server service.

You "may" be able to get the database running by executing the following command in a query window: RESTORE DATABASE [xxx] WITH RECOVERY;

You could, although I would not normally recommend this, alter the database to automatically close after there are no active connections.

To accomplish this, you would execute the following query:

ALTER DATABASE [xxx] SET AUTO_CLOSE ON WITH NO_WAIT;

score 2 · Answer 3 · answered Jan 13 '16 at 18:43

2

Another way that works is to "Restart" the Database Engine. If feasible and/or practical for this server, it may be faster whenever you have several DB in the external drive.

In SQL Server Management Studio:

Attach the external drive
right-click on the database engine : Server Name(SQL Server 12.0.2000 ... etc)
Select "Restart"
Answer Yes when asked if you want to proceed

answered Jan 13 '16 at 18:43

Ignacio

64
1
3

this Answer Works for me – Hussein Khalil Nov 06 '17 at 13:24

score 1 · Answer 4 · edited Feb 12 '15 at 00:02

1

Below worked for me:

Run SQL Management Studio as Administrator (right click on SQL Management Studio icon and select 'Run As')
Take database offline
Detach the database using DROP option
Attach the database
If you were using this database with a Web App running on IIS then you may need to restart the IIS Server

Hope this helps someone

edited Feb 12 '15 at 00:02

Anima-t3d

3,431
6
38
56

answered Feb 11 '15 at 23:29

Ainsof

124
1
1

score 0 · Answer 5 · answered Feb 11 '21 at 11:48

If the SQL Server knows that database recovery needs to be run but something is preventing it from starting, the Server marks the db in ‘Recovery Pending’ state. This is different from the SUSPECT state because it cannot be said that recovery is going to fail – it just hasn’t started yet.

Check this thread: How to fix Recovery Pending State in SQL Server Database?

MSSQL database on external hard drive shows Recovery Pending

5 Answers5