How to fix Recovery Pending State in SQL Server Database?

Question

How to fix Recovery Pending State in SQL Server Database?

Answer 1

One way to end up with a database that is in "Recovery Pending" state, is in the context of restoring a backup of an encrypted database (encrypted with TDE) on a new SQL Server instance.

• You restored the backup of the master key on the new SQL Server instance,
• You recreated the encryption certificate on the new SQL Server instance,
• You restored a backup of the encrypted database on the new SQL Server instance,

But:

• You forgot to add an encryption of the master key by the service master key on the new SQL Server instance.

Adding this encryption by the service master key is needed to enable the automatic decryption of the database master key. Add this encryption as follows:

USE MASTER
GO

OPEN MASTER KEY 
    DECRYPTION BY PASSWORD = 'your_master_key_password_here';
GO

ALTER MASTER KEY 
    ADD ENCRYPTION BY SERVICE MASTER KEY;
GO

CLOSE MASTER KEY;
GO

Afterwards, take the affected database offline, then put it back online.

Answer 2

1. Run SSMS as Administrator

2. Right click the DBName then Tasks->Take Offline to make it offline

3. Right click the DBName then Tasks->Detach so it should be disappear from list of DBs

4. Right click the Databases->Attach , click on the Add.. then chose the .mdf file of your DBName

It should works again

Answer 3

Detach, re-attach, solved !

ALTER DATABASE MyDatabase SET EMERGENCY;
EXEC sp_detach_db MyDatabase
EXEC sp_attach_single_file_db @DBName = MyDatabase, @physname = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\MyDatabase.mdf'

Alternavely, using Management Studio, you can:

1. Make sure you know where you database files are located;
2. Target database > righ-click > Tasks > Take offline;
3. Target database> righ-click, Tasks > Dettach;
4. Databases (above) > righ-click, Tasks > Attach;

Answer 4

This could happen due to insufficient permissions for a folder with database files (in my case due to a domain migration).

Just give access to the folder for an SQL service's account.

Answer 5

I was using azure, the mdf and log file are in different disk and not attached that disk with which it is not able to figure that files and hence the file Recovery Pending

Answer 6

Ensure that the "Log On" account for the "SQL Server (201x)" service (listed in Windows Services (Manager)) has sufficient rights. You may try changing it to another Logon. In my case, changing it from "This account" to "Local System account", restarting the "SQL Server (xxxx)" service and SQL Server Management Studio (SSMS), and logging into SSMS again resolved the issue.

Background (in my particular case): I had 3 different instances of SQL (2008r2, 2012 and 2014) running on my local PC, and was busy moving a folder (which I later discovered contained some SQL data & log database files) to another PC. Halfway through, I stopped the SQL Services in Service (Manager), hoping that the files would move across without issues - since they would now, no longer be in use. I realized that I needed to confirm the database names, and file locations in SQL (in order to set them up again on the new pc), so I copied the SQL data and log files back (to the original locations). After restarting the PC - the majority of the databases on various instances all showed as: "Recovery Pending" in SSMS. After first trying the procedure from stellarinfo (listed as an answer here by @Mahesh Thorat) on 2 of the databases, and still not having any luck, a post SQL SERVER – Where is ERRORLOG? Various Ways to Find ERRORLOG Location on Pinal Dave's SQL Authority website and the post: Operating System error 5(Access is Denied) on SQL Server Central gave me an idea that it could be rights related after looking at the SQL Errorlog and finding "Operating system error 5: "5(Access is denied.)". Another post Msg 3201, Level 16 Cannot open backup device. Operating system error 5(Access is denied.) at SqlBak Blog seems to support this.

Answer 7

In my case, this affected the secondary server in a High Availability SQL Server cluster. The primary was Synchronizing but the secondary was Recovery Pending.

After checking in cluadmin.msc, realised that the secondary server wasn't healthy in the cluster. Then determined Cluster Service had failed to start on the second cluster box after a Windows Update enforced reboot (may have happened because the file share witness was rebooting after a similar Windows Update at the same time).

Starting the Cluster Service brought the databases back into Synchronizing status.

Answer 8

In our case it was caused by the disk drive running out of space. We deleted some junk to free space, then fixed the "Recovery Pending" by stopping and restarting the SQL Server Service.

Answer 9

While using SQL Management Studio, there is an intermittent issue when a user is changing the Database Names then sometimes SQL Server uses the same DB file for two different Databases. If SQL Server is in this state then you would be probably seeing the following error if you try Mahesh's answer:

"The process cannot access the file because it is being used by another process"

To fix this issue:

1. Stop MSSQLServer service from the Services Console MMC
2. Rename the DB and the Log files (Database Properties -> Files)
3. In the Object Explorer window in SQL Management Studio, refresh the 'Databases Folder', if you see that there is another Database node (in addition to the one which you are trying to rectify this issue) which is shown in 'Recovery Pending State' then proceed to the next step. If you do not see this error then you need to try something else to resolve this issue
4. Make a backup of the DB and Log files for new offending node from step 3 and !!Careful!! delete the database
5. Restore the Db File names which you changed in Step 2
6. Now start the MSSQLServer service from the Services Console
7. Re-try the steps from Mahesh's answer

Answer 10

When your Database .mdf file name is renamed, this issue is occurred. To solve:

Restart SQL EXPRESS in Services, Pending issue is solved.

Answer 11

Execute the following set of queries:

ALTER DATABASE [DBName] SET EMERGENCY;
GO

ALTER DATABASE [DBName] set single_user
GO

DBCC CHECKDB ([DBName], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO 

ALTER DATABASE [DBName] set multi_user
GO

For more info: https://www.stellarinfo.com/blog/fix-sql-database-recovery-pending-state-issue/