Sql-server – What are the “additional actions” available for “Recovery Pending” databases

recoverysql serversql-server-2012

I've used powershell to run a sql script on several databases on a test server. Disk space was low, so the script failed on 34 out of 75 databases. All fair enough.

In any case, afterwards 10 of my 75 databases ended in a modus where SSMS appends to the database name "(Recovery Pending)". I've solved the disk space issue and waited for a bit, but the databases remain in this state.

The MSDN carefully explains this state (emphasis mine):

SQL Server has encountered a resource-related error during recovery. The database is not damaged, but files may be missing or system resource limitations may be preventing it from starting. The database is unavailable. Additional action by the user is required to resolve the error and let the recovery process be completed.

Great! But what are these "additional actions", and how can I find out which one's for me?

My Google-fu turned up several hazy forum posts with solutions such as:

  • detach then attach again
  • take offline then take online again
  • restore a backup
  • setting emergency/single_user and back to multi_user
  • restart the sql server instance

Some of these worked for me, heck: all of them may work. However, these are all "just try this!" suggestions from questionable sources with (side) effects I may not know about or even want.

So, again: what is the appropriate "additional action" msdn speaks of, and above all: why??

Best Answer

Since this is a test server, the best choice would be simply to run RESTORE DATABASE [db_name] WITH RECOVERY; for each database that is in Recovery Pending state. Check the Microsoft Docs page for RESTORE DATABASE with recovery only for further details.

This code will generate the statements for all databases in recovery pending:

SELECT 'RESTORE DATABASE ' + quotename(d.name) + ' WITH RECOVERY;'
FROM sys.databases d
WHERE d.state_desc = 'RECOVERY_PENDING'
ORDER BY d.name;
Related Question