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.

Answer 1

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:

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

Answer 2

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;