Shulou(Shulou.com)06/01 Report--

In this article, the editor introduces in detail "how to repair the recovery pending state in the SQL Server database". The content is detailed, the steps are clear, and the details are handled properly. I hope this "how to repair the recovery pending state in the SQL Server database" article can help you solve your doubts.

Reasons for putting the SQL database in recovery mode

The SQL server recovery hang may be due to improper system shutdown during parsing some transactions in the database.

When there is not enough space in your hard drive or RAM, sometimes running various queries will cause the SQL database to resume a suspended state. Various users move their log files so that they can increase the throughput of the system, but when transferring SQL files, their data is corrupted, which forces the database to be restored to a suspended state.

Phases in the SQL Server database

There are three management phases in the SQL Server database, which are divided according to the severity of the damage.

Online phase-if the user is performing a query while working on the SQL database server and a database file is corrupted during this process, the database remains online and remains accessible online. This phase is called the online phase.

Suspect phase-if the server cannot recover the database when the SQL application starts, SQL marks the database as suspicious.

Recovery suspend phase-this phase occurs when SQL Server knows that database recovery must be run but what prevents it from starting. In this case, SQL Server marks the DB as "Recovery Pending".

You can check the suspended database from the line by running the following command on the SQL database.

From sys.databases SELECT name, state_desc and write GO and press enter to view the SQL database recovery pending status as

You can see that the Demo at position 6 in the result is a database of RECOVERY PENDING STATE.

Now let's take a look at how to resume the pending phase of SQL server recovery.

Manual recovery of pending state in SQL Server database

The manual approach to solving SQL database problems in the recovery model is done in two steps. Before we study these steps, I suggest making a copy of your SQL database so that if you cannot parse these steps, your data will not be corrupted.

Step 1

Check SQL in emergency mode and force it to be fixed. "use the Alter DB and CHECKDB commands to delete the MS SQL restore pending state, as follows:"

ALTER DATABASE [DBName] SET EMERGENCY

ALTER DATABASE [DBName] set single_user

DBCC CHECKDB ([DBName], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS

ALTER DATABASE [DBName] set multi_user

SQL helps their users by freeing the SQL server from the emergency phase, but if the above command does not help you solve the problem, then

Use the following command to put the database in emergency mode

ALTER DATABASE mydatabase SET EMERGENCY . After you use this command, the emergency tag is placed after the file.

Now you must set the SQL database back from emergency mode to normal mode by applying the following query

ALTER DATABASE mydatabase SET ONLINE .

Step 2

Set the database to emergency mode and detach and reconnect the primary database

To perform this step to overcome the SQL server recovery Pending state, you need to perform the same steps as you did in step 1 to place the database in a state of emergency. This user needs to take MS SQL offline by running a query and then go online again to overcome the MS SQL resuming pending state.

ALTER [DBName] SET EMERGENCY

ALTER DATABASE [DBName] set multi_user

EXEC sp_detach_db'[DBName]'

EXEC sp_attach_single_file_db @ DBName ='[DBName]', @ physname = N' [mdf path]'

Using the second step, the user will be able to create a new log file, and the corrupted file will be automatically deleted from the database. Let's take a look at the problems you will encounter when using steps 1 and 2 to eliminate the SQL server recovery hang problem.

Manual method limit

When repairing a recovery pending state in an SQL Server database, the database can be highly corrupted.

If you are not skilled or come from a technical field, you will not be able to run these commands well, resulting in confusion in SQL database files.

Manually bringing the SQL server online or offline may completely damage your application, so the relational database will suffer a great setback, causing the entire installation application to crash.

Now let's turn to another way to control the SQL server database recovery hang problem.

Professional recovery pending state in SQL Server database

The professional method to overcome the SQL server recovery hang problem is better than the manual method. In this regard, we use the SQL database recovery tool to repair and recover corrupted files in the SQL database and to help you repair the recovery pending state in the SQL server database. This tool supports MDF and NDF files. Use this utility to recover your database tables, triggers, records, functions, and stored procedures.

Bearing in mind the importance of data, this utility enforces two standard and advanced recovery models. After repairing using the SQL database recovery tool, users can also save files from one SQL server to another. This tool welcomes all versions of windows. Architecture and snapshots can also be considered when using this software.

"after using the SQL database recovery tool, you will not encounter any problems repairing the SQL Server database recovery pending state."

After reading this, the article "how to repair the recovery pending state in SQL Server database" has been introduced. If you want to master the knowledge points of this article, you still need to practice and use it yourself. If you want to know more about related articles, welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.