What is the procedure for putting a SQL Server database into recovery mode?

Changing the recovery mode of a SQL Server database

You could be asking how you’d want to place a database into an undesirable state, such as Recovery Pending. In my scenario, I had a client database that had become inaccessible due to storage failure. Fortunately, this database was not in use in production, so I had some time to figure out how to fix the problem.
“Try Before You Pry” was a phrase I heard a lot throughout my time in the fire department. Is it necessary to kick in the house’s front door? I could open it and avoid damaging the door by attempting before prying (with my boot). This philosophy holds in various types of situations. It’s good to test things out on non-critical databases first to avoid further damage.
In this case, I’d like to give it a shot before forcing something that could be harmful. It meant I had to get a test database back up and running. Once it’s in the state, I want it to be. I can try various approaches to recover the database correctly.
Once a successful solution establishes, I may confidently pry against the broken database in production, knowing that I am using a proven solution.
DISCLAIMER: DO NOT USE A PRODUCTION SQL SERVER FOR THIS.
You sample notice.
DISCLAIMER: DO NOT USE A PRODUCTION SQL SERVER FOR THIS.
You give sample notice. Again.

How do you get a SQL Server database into a recovery pending state?

It is how I accomplished it: 

1. Click the “New Transaction” button to start a new transaction.
2. Make a fresh table. 
3. Should stop SQL Server. 
4. The database log file should be renamed or deleted. 
5. Should restart SQL Server Service. 

The database will be in recovery, waiting for the state when the SQL Server service restarts.

What is the reason for the database’s pending recovery?

The database will be put into a recovery pending condition when it tries to come back up since the log file is missing, but there was an ongoing transaction when shut the service down. SQL Server would proceed through the transaction log recovery phase under regular operations even with an open transaction. During the rollback phase of recovery, SQL Server will attempt to roll back and undo all available transactions at the time of restart. It is unable to do so since the log file no longer exists.

As a result, the database is now available for recovery. Because there was an open transaction, SQL Server could not get the database into a consistent state. It is pending recovery. The error log will look somewhat like this: SQL Server will regenerate the transaction log file if the database is shut down properly and the transaction log file is removed/renamed/etc.

Summary

It can sometimes be helpful to put a database in a specific state so you may test solutions before attempting to act in a Production environment. Just remember to give it a try before trying to pry it open. Being extra careful is not harmful because not doing so could worsen matters.

About Enteros

IT organizations routinely spend days and weeks troubleshooting production database performance issues across multitudes of critical business systems. Fast and reliable resolution of database performance problems by Enteros enables businesses to generate and save millions of direct revenue, minimize waste of employees’ productivity, reduce the number of licenses, servers, and cloud resources and maximize the productivity of the application, database, and IT operations teams.