2

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??

  • If you've fixed the resource issue, I don't see why you couldn't just use RESTORE [DB] WITH RECOVERY – Sean says Remove Sara Chipps Jul 2 '14 at 11:05
  • 1
    You got recovery pending because database could not grow further due to space constraint and thus could not restore giving recovery pending. Ideally after space is added it should work with restore database with recovery if not you have to start restore process again. – Shanky Jul 11 '14 at 12:31
1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.