How to Recover a Database from Suspect or Recovery Pending State in SQL Server?

So one or more of your databases went into RECOVERY PENDING state or in SUSPECT mode and you are confused about what to do now to fix the issue immediately.

Do not panic 😨 Almost every DBA has encountered this scenario quite a few times in their professional career where one or more databases didn’t come ONLINE after scheduled maintenance or due to an unexpected server reboot.

I know you are probably in an incident scenario and on a tight SLA to bring the database back online. So, let’s jump into resolving the issue first and make sure to check out the reason behind it, once the database is online.

List out the databases which are not online:

For this demo, we will use a database named dbaguides which is currently in RECOVERY PENDING state.

Let’s verify the current state of the databases.

select name, state_desc 
from sys.databases
where state_desc <> 'ONLINE'

Bring the database back online:

1. Put the database in Emergency mode first. It puts the database in READ_ONLY mode, disable logging and become accessible to the sysadmins only.

ALTER DATABASE [dbaguides] SET EMERGENCY;
GO

1. Put the db in SINGLE_USER mode.

ALTER DATABASE [dbaguides] set single_user
GO

1. Perform DBCC CHECKDB to recover the database.

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

The REPAIR_ALLOW_DATA_LOSS option may result in some data loss.

Certain errors, that can only be repaired using the REPAIR_ALLOW_DATA_LOSS option, may involve deallocating a row, page, or series of pages to clear the errors. Any deallocated data is no longer accessible or recoverable for the user, and the exact contents of the deallocated data cannot be determined.

Microsoft always recommends a user restore from the last known good backup as the primary method to recover from errors reported by DBCC CHECKDB. The REPAIR_ALLOW_DATA_LOSS option is an emergency “last resort” option recommended for use only if restoring from a backup is not possible.

1. Put the database in MULTI_USER mode now.

ALTER DATABASE [dbaguides] set multi_user
GO

Let’s check the database state now.

select name, state_desc 
from sys.databases
where name='dbaguides'

Voila 🤩 The database is online and accessible now 🔥

Alternatively, you can use the database detach-attach method as well. The sp_attach_single_file_db command attaches the database to the server by re-building a new log file altogether.

ALTER DATABASE [dbaguides] SET EMERGENCY;
ALTER DATABASE [dbaguides] set multi_user;
EXEC sp_detach_db [dbaguides];
EXEC sp_attach_single_file_db @DBName = [dbaguides], @physname = N'D:\SQL Server\Data\dbaguides_dat.mdf'; --Specify the .mdf file path

However, try to avoid using the database detach-attach method. The sp_attach_single_file_db command feature is currently in maintenance mode and it may be removed in a future version of SQL Server. Refer here for details.

Now that the database is recovered, let’s do some RCA 💡

What does the database RECOVERY_PENDING state signifies?

The database RECOVERY_PENDING state indicates that the database can’t start the crash recovery process. This could be due to either of the below two points –

• SQL Server cannot access the boot page of the primary data file to determine whether a crash recovery is needed.

• SQL Server has already determined that a crash recovery is needed, but it couldn’t start the recovery process maybe because the transaction log file is missing or corrupted.

The database RECOVERY_PENDING state was introduced in SQL Server 2008 release. Before that, during SQL Server 2005 or earlier era, there used to be only one state – SUSPECT mode.

So, what does the database SUSPECT mode signifies? 🤔

The database SUSPECT mode indicates that the database recovery process has been started, but it failed to complete and that the database couldn’t be brought online for user connections. Possible reason for a database to go into SUSPECT mode is that if the transaction log file becomes inaccessible or damaged due to some reason and preventing a transaction rollback process to complete.

Essentially, the solution to recover database from both the problem – database SUSPECT mode or RECOVERY_PENDING state, is same. You can follow the above steps in order to bring the database back to ONLINE.

And yes, there are a number of 3rd-party database recovery tools also available in the market, which can help you to recover database in a much easier way.

Hope you found this article informative and it has helped you to timely resolve your issue.

Happy Learning 🙏