Have you ever encountered a situation where your SQL Server database was in a state of “RECOVERY PENDING” or “SUSPECT”? If so, you may have wondered what exactly these states mean and how they differ from each other. In this blog post, we will explore the differences between these two states and shed some light on their significance.

Let’s start by understanding what happens when a database is restarted. When a database is restarted, it goes through a recovery process to bring it back online. During this process, the database checks the integrity of its files and performs any necessary repairs to ensure data consistency.

When a database is in the “RECOVERY PENDING” state, it means that the recovery process could not start at all. This state is typically caused by issues such as incorrect file paths, missing files, or incorrect permissions on the files or folders. In other words, the database is unable to access the necessary files to complete the recovery process.

On the other hand, when a database is in the “SUSPECT” state, it means that the recovery process was started but failed in one of the three phases: analysis, redo, or undo. This failure marks the database as suspect, indicating that there may be corruption or other issues that need to be addressed.

It is important to note that the concept of “SUSPECT” was introduced in SQL Server 2005. Prior to that, databases in a failed recovery state were simply marked as “RECOVERY PENDING”. The introduction of the “SUSPECT” state provided a more granular indication of the recovery process failure.

Now, let’s consider a real-life scenario to illustrate the difference between these two states. Imagine you have recently moved all your user databases to a new drive and restarted SQL Server. However, upon restarting, you notice that one of the databases is in the “RECOVERY PENDING” state. After investigating the issue, you find that the service user account does not have the necessary permissions on the new drive. By granting the appropriate permissions and restarting the service, you are able to resolve the issue and bring the database back online.

In summary, the “RECOVERY PENDING” state indicates that the recovery process could not start due to issues such as incorrect file paths or permissions, while the “SUSPECT” state indicates that the recovery process started but failed in one of the three phases. Understanding these states can help you diagnose and resolve issues with your SQL Server databases more effectively.