How to programatically detach SQL databases in recovery pending status (4040433)

In certain situations, AppAssure/Rapid Recovery attachability check does attempt to detach the databases to verify before the recovery point, they belong to is dismounted. These databases may be easily recognized as they bear specific names (the "AA-" prefix, the actual database name, and a GUID).
 

For instance, after a failed detach operation, the database named "Zero Demand" may be found in the SQL management Studio under a name similar to "AA-Zero Demand-80dc9b5a-9287-4e28-8059-dcf5c2d0b2c4", with a RecoveryPending status.

In some cases, the number of these databases grows in the hundreds. 
 

This issue may become a problem for Rapid Recovery if the option to perform the attachability tests on the server of origin is enabled, as the attachability tests would fail and burden the SQL server with countless error messages. Moreover, at times, backups may fail due to those databases as well. 
 

You want to have an easy way of removing these ghost databases and even perform some automatic maintenance.

 

This script is provided "as is" for the purpose of illustrating how Rapid Recovery tasks may be performed in conjunction with Microsoft PowerShell. Quest shall not be liable for any direct, indirect, incidental, consequential, or other damage alleged in connection with the furnishing or use of this script or of the principles it demonstrates.

 

Depending on the magnitude of the issue, there are quite a few options to address the issue.

For instance, knowledge article "How to Remove Previously Mounted Databases from the Local SQL Instance on the Core " presents how to do it via the SQL Management Studio, either manually or SQL scripting. In very rare situations, knowledge article "How to Detach Databases "used for replication " after failed Attachability Checks" needs to be used to detach some of these databases successfully.

The current KB article presents a PowerShell Script that performs the database detach operation.

The script allows either removing all AppAssure/Rapid Recovery related databases on all installed local SQL server instances or detach all databases in "Recovery Pending" mode (default option). Databases in "Recovery Pending" mode have a missing or corrupted log or database file. Please note that if both the database and log files are missing, SQL may report the database as being in a normal state. To work around this issue, all databases that are not in RecoveryPending mode are checked for pertinent files over all filegroups and log files. If any files are missing, the database is detached.

In order to remove only the SQL databases related to AppAssure/Rapid Recovery, the -aaonly parameter. If in use, this parameter prompts the script to detach all databases identified as AppAssure/RapidRecovery mounts. If the parameter is missing, all databases in all SQL instances present on the protected server in recovery mode are detached. In both cases, the user running the script needs to have SQL sysadmin permissions.

For a fully automatic process, the script may be used at the beginning of any AppAssure/Rapid Recovery transfer on the agent after being modified to fit this functionality and renamed to pretransferscript.ps1. In this particular case the agent needs to reside in "C:\Programdata\Apprecovery\scripts" AND the local SYSTEM account needs to be given SQL sysadmin permissions on all SQL instances residing on that server. Please note that the detailed results of running the pretransferscript.ps1 are logged in the Apprecovery logs AND that in this format, the script will detach all databases that are in recovery mode OR one of more of their component files cannot be found. This ensures that the backups are successful but requires caution. The detached database files are preserved and backed up offline, but the database administrator needs to be aware that an issue was detected. 

Both versions of the script -- the manual one and the modified to work at the beginning of each transfer are attached to this KB and packed in a ZIP file.