Summary: This post will offer you reasons for recovery of pending state in SQL Server. Also, it will provide you a quick method to fix "SQL Server database in recovery pending state" problem.
If one or more core files of SQL are in inconsistent state then SQL database is considered to be damaged. Here are the states of damage.
Online –While executing a query, if one of the data files is damaged, the database will remain online.
Suspect –If the database can't be recovered during backup of SQL server, the database is marked as suspect.
Recovery Pending –If the SQL Server requires to run database recovery, but something is preventing from starting, the server considers the db in 'Recovery pending' state.
Here is one smart way to fix recovery pending in SQL database.
Database EMERGENCY mode considers the database as READ ONLY, disable logging, and allow access only to system admin. By setting database in this mode will bring inaccessible database online.
Once you open database in EMERGENCY mode, try repairing the database using DBCC CHECKDB command with the REPAIR ALLOW DATA LOSS option. To do this, Open SSMS and execute the queries mentioned below:
ALTER DATABASE (Database Name) SET EMERGENCY;
GO
ALTER DATABASE (Database Name) Set Single _User;
GO
DBCC CHECKDB (Database Name), REPAIR _ALLOW_ DATA_ LOSS) WITH ALL_ ERRORMSGS;
GO
ALTER DATABASE (Database Name) SET Multi _User;
GO
In this mode, we have to first work on Emergency mode.
ALTER DATABASE (Database Name) SET EMERGENCY;
ALTER DATABASE (Database Name) Set Multi _User;
EXEC sp_detach _db (Database Name)
EXEC sp_attach_single_file_db @DB Name = '(db Name)' @physname= N'(mdf path)
This query will get rid of corrupt log and build a new one automatically.
We have clearly discussed in this blog how important SQL database is for users and how one can recover SQL Server database in pending state.
