MSSQL database on external hard drive shows Recovery Pending

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

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;

Tags:

Sql

Sql Server