<< SQL Server Hosting - HostForLIFE :: CROSS JOIN In SQL | SQL Server Hosting - HostForLIFE :: Customized Auditing In SQL For DDL Operations At Server Level >>

SQL Server Hosting - HostForLIFE :: How to put a SQL Server Database into Recovery Pending?

August 2, 2021 06:52 by author

Peter

You might be asking why on earth would you want to get a database into an undesirable state, more specifically into a Recovery Pending state. Well, in my case, I had a client database that had entered into this state due to a failure of storage. Thankfully, this database was not being used for production, so I had some time to determine the best way to fix the issue.

A phrase that was often used during my time in the fire service, was "Try Before You Pry". Does the front door of the house need to be kicked in? It may be unlocked and by trying before prying (with my boot) I can prevent damage to the door. In these types of scenarios, this philosophy holds true. Try things out on non-critical databases will help prevent any further damage.

In this instance, I want to try it before forcing something that might be damaging. This meant I had to get a test database into a recovering state. Once it is in the state I need, then I can attempt different methods to recover the database properly. Once a successful solution is determined, I can then confidently pry against the damaged database in production knowing that I am using a validated solution.

DISCLAIMER: DO NOT DO THIS ON A PRODUCTION SQL SERVER INSTANCE.

You have been warned.

DISCLAIMER: DO NOT DO THIS ON A PRODUCTION SQL SERVER INSTANCE.

You have been warned. Again.
How do you get a database into a recovery pending state?

Here is how I did this:

    Start a new transaction.
    Create a new table.
    Stop the SQL Server service
    Rename/Delete the database log file.
    Restart the SQL Server Service

The database will be in a recovery pending state upon the restart of the SQL Server service.
Why is the database in recovery pending?

When the database attempts to come back online, it will be put into a recovery pending state because the log file is not present but there was an open transaction when the service was shut down. In normal operations, even with an open transaction, SQL Server would go through the recovery phase of the transaction log. In the rollback phase of recovery, SQL Server would attempt to rollback and transaction that was open at the point of restart and undo the changes. Since the log file no longer exists, it is unable to do so.

Therefore, the database is now in the recovery pending status. It’s pending recovery because there was an open transaction, but SQL Server is unable to bring the database into a consistent state.