ASNA DataGate for SQL Server (DSS) enables DataGate connectivity for Monarch, Synon Escape, or Visual RPG for .NET applications. DataGate for SQL Server, like DataGate for IBM i, doesn’t provide the database; rather both of these products provide connectivity to the underlying database.

In the case of DSS, the underlying database is provided by Microsoft’s SQL Server. Usually, for application development work you’ll DSS to SQL Server with either the ASNA COM-based Database Manager or the .NET-based DataGate Explorer. However, there may be times when you need to use SQL Server’s own SQL Server Management Studio to work directly with the SQL Server database.

SQL Server is a robust and reliable database. But disaster can strike and one thing to watch for is the dreaded "Recovery Pending" error message shown in SQL Server Management Studio (shown below in Figure 1).

<small>Figure 1. The "Recovery Pending" message in SQL Server Management Studio</small>

This error manifests itself with the inability to connect or otherwise work with the database in any way. This error condition is typically caused by insufficient disk space–but it may also be caused by errant behavior such as a power interruption. It’s important to understand that DataGate for SQL Server doesn’t cause this error–this error is the result of underlying issue (usually database corruption) directly with SQL Server. When this error occurs the database is effectively offline and the error needs explicit recovery action.

Recovering from this SQL Sever error is not only beyond the scope of this article but it’s also beyond the scope of ASNA Technical Support. Probably the best thing to do is to restore the database from a backup–assuming there is one available. At the very least, you’ll most likely need to have your in-house SQL Server expert available.

What to do

There are many instructions on the Web about how to correct the “Recovery Pending” error in place, but be warned, the steps required are fiddly and vary from one article to another (and assume more than a passing knowledge of SQL Server). Depending on your situation, you may also want to look at data recovery tools. These tools (examples of which are herehere, and here) aren’t free (usually starting at about $400 USD), but may be worth the expense if they recover your precious data. Shop carefully and look for money-back guarantees if they don’t solve your problem.

The big lesson: backup regularly

It is crucial that you’re backing up your SQL Server databases on a timely basis. Perform a fire drill to make sure you that you are. This fire drill should go beyond ensuring backup binaries are produced–make sure you can restore from them (and do this drill frequently!). Also be sure to keep a recurring watch on disk space usage. SQL Server isn’t very forgiving when it runs low. The full version of SQL Server has a featured called the SQL Server Agent and that facility enables automating recurring database backup. SQL Server Express doesn’t have the SQL Server Agent feature so you’ll need to roll your own automated backup (perhaps with a stored procedure) or use a third-party backup utility.