Send Alerts for Recovery Pending DBs

Script to send an alert when a DB is in recovery pending mode.

DECLARE @recoveryPendingDBs TABLE (name NVARCHAR(128));
DECLARE @mailProfileName NVARCHAR(128) = 'mailprofile';
DECLARE @operatorEmail NVARCHAR(100);

INSERT INTO @recoveryPendingDBs
SELECT name 
FROM sys.databases 
WHERE state = 3
AND name NOT IN ('Replication TestDB');

IF EXISTS (SELECT 1 FROM @recoveryPendingDBs)
BEGIN
    DECLARE @subject NVARCHAR(100) = 'CRITICAL: Database(s) in Recovery Pending State';
    DECLARE @body NVARCHAR(MAX) = 'The following databases are in RECOVERY_PENDING state and may need manual intervention: ' + 
        (SELECT STRING_AGG(name, ', ') FROM @recoveryPendingDBs);
    
SELECT @operatorEmail = email_address 
FROM msdb.dbo.sysoperators 
WHERE name = 'mail';

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'mailprofile',
    @recipients = @operatorEmail,
    @subject = @subject,
    @body = @body;
    
END
ELSE
BEGIN
    PRINT 'No databases in recovery pending state.';
END