1

We have a backup from a SQL 2008 instance. When we restore it to a SQL 2016 instance and change it to the proper compatibility level, it restores fully recovered. Then after a few minutes, it drops into Recovery Pending status. We can't do anything with the database (i.e. ALTER DATABASE...) until we restart the SQL instance. Then it does the same thing. Starts up fully recovered then drops into Recovery Pending' status. How can I find out what is causing it to go into the Recovery Pending status?

  • 3
    What exact command are you using to restore? Did you look in SQL Server's error log? Are any of the data/log files on drives that are full (or close)? What does "can't do anything" mean? Did you try setting to EMERGENCY? What was the actual result (not "couldn't")? – Aaron Bertrand May 10 at 20:28
  • database isn’t cleanly shut down? Mark database in Emergency mode, detach the main database and re-attach it.. Check this for more info stellarinfo.com/blog/… – CR241 May 10 at 22:37
  • 1
    Did you check the ErrorLog of the SQL server? It provides usually helpful informations, why it can't restore / attach / take onlie a database – Thomas Franz May 11 at 12:25
  • There is going to be a line or two in the error log telling you why the state changed. Add that to your post. – Adam May 11 at 16:00
0

SQL SERVER uses the status of Recovery Pending to point you to the error log. It’s a fail-safe feature that says something prevented opening the database but is not an alarm like the SUSPECT state is!

RECOVERY PENDING

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.

Database States - Microsoft Docs

Solution:

Check your Error Log for why it is not able to recover and let us know.

For example, an issue I have seen on a VM is after a reboot, several disks were pointed to fileshares that came online after the Instance was already up. This resulted in the same status, but was easily resolved by

RECOVER DATABASE [DB] WITH RECOVERY;

We later moved it to a regular drive and the issue was resolved for future reboots.

Therefore check the log and make sure everything is ok before running the statement.

0

Sorry folks. Been really swamped. Turns out we don't need this database. After restarting SQL instance, I was able to DROP the database before it went to RECOVERY PENDING mode.

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.