Automate SSRS encryption key backup with Powershell script

Backup SSRS Encryption key with Powershell: 

I recently had a requirement to automate the SSRS encryption key automation. 
All you need to do is create below tables. Passwords are needed for each server to use while taking SSRS encryption key backup. 

• A table with Server and Password (I used pwd) columns.
• A table to keep track of history of Successful SSRS encryption key back up for each server. (you may include these columns Servername, Instance, FileName, EncryKeypwd, Backupdate, FileLocation)

Below is the script it will do the rest: All you need to do is create a job to run the script with schedule as you required. Please leave a comment if you have any questions. Hope it helps.

# $Computers grabs list of Servers with SSRS on them
$Computers = invoke-sqlcmd -ServerInstance "DBASQLInstanceName" -Database "DBAdb" -Query "SELECT [Server]  FROM [DBAdb].[dbo].[SSRS_ServerList_with_Pwds]"
 
#for each separate server in $Computers
foreach ($Computer in $Computers) 
{
$ComputerName=$Computer.Server

# Folder path to keep the backup file
$KeyFolder = "\\YOUR SHARE WHERE YOU WANT TO KEEP THE BACKUPS\SSRS\$ComputerName\"

# Try Catch Block for Catching the condition if the folder doesn't exists,…stop flow
$Error.Clear()
Try
    {
      # Delete files older than 30 days from above backup folder
      Get-ChildItem Microsoft.PowerShell.Core\FileSystem::$KeyFolder -ErrorAction Stop| where { $_.LastWriteTime -le ((Get-Date).AddDays(-30))} | remove-item 
    }

catch
    { 
       echo $_.Exception.GetType().FullName, $_.Exception.Message
       $From = "YourFromEmailID"
       $To = "YourToEmailID"
       $Subject = "SSRS Encryption Key Backup Failed for $computername"
       $Body = "$_.Exception.Message"
       $SMTPServer = "Your SMTP Server"
       $SMTPPort = "Your SMTP Port"
       Send-MailMessage -From $From -to $To -Subject $Subject `
       -Body $Body -SmtpServer $SMTPServer -port $SMTPPort
     }

# Query table to dig password and save it to variable KeyPassword
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=DBASQLInstanceName;Database=DBAdb;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "SELECT pwd FROM DBAdb.dbo.SSRS_ServerList_with_Pwds where server='$ComputerName' "
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$KeyPassword=$DataSet.Tables[0].rows[0].pwd


# Checking for Password doesn't exsits or blank or NULL
If ([string]::IsNullOrWhiteSpace($KeyPassword)) 
      {
       Write-Host “Password for Encryption Key doesn't exsits on SSRS_ServerList_with_Pwds table for $computername"
       $From = "YourFromEmailID"
       $To = "YourToEmailID"
       $Subject = "SSRS Encryption Key Backup Failed for $computername"
       $Body = "No Password found on SSRS_ServerList_with_Pwds table for $computername "
       $SMTPServer = "Your SMTP Server"
       $SMTPPort = "Your SMTP Port"
       Send-MailMessage -From $From -to $To -Subject $Subject `
       -Body $Body -SmtpServer $SMTPServer -port $SMTPPort
       #$host.Exit()
      }

# if password is good proceed 
else {
# Try to take SSRS Encryption key backup
$Error.Clear()
Try
{

$TimeStamp = Get-Date -Format "-yyyyMMdd-HHmmss"

Get-WmiObject -Namespace "Root\Microsoft\SqlServer\ReportServer" -Class "__Namespace" -ComputerName $ComputerName |
    Select-Object -ExpandProperty Name |
    % {
        $NameSpaceRS = $_
        $InstanceName = $NameSpaceRS.SubString(3)
        $KeyFileName = Join-Path -Path $KeyFolder -ChildPath ($InstanceName + $Timestamp + ".snk")
        $SQLVersion = (Get-WmiObject -Namespace "Root\Microsoft\SqlServer\ReportServer\$($NameSpaceRS)" -Class "__Namespace" -ComputerName $ComputerName).Name
        $SSRSClass = Get-WmiObject -Namespace "Root\Microsoft\SqlServer\ReportServer\$($NameSpaceRS)\$($SQLVersion)\Admin" -Query "SELECT * FROM MSReportServer_ConfigurationSetting WHERE InstanceName='$($InstanceName)'" -ComputerName $ComputerName
        $Key = $SSRSClass.BackupEncryptionKey($KeyPassword)
        If ($Key.HRESULT -ne 0) {
            $Key.ExtendedErrors -join "`r`n" | Write-Error
        } Else {
            $Stream = [System.IO.File]::Create($KeyFileName, $Key.KeyFile.Length)
            $Stream.Write($Key.KeyFile, 0, $Key.KeyFile.Length)
            $Stream.Close()
        }
    }

# Update History table
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=DBASQLInstanceName;Database=DBAdb;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "INSERT INTO dbo.SSRS_Encryption_Key_Bkp_History
        ( Servername, Instance, FileName, EncryKeypwd, Backupdate, FileLocation )
VALUES  ( '$ComputerName' , -- Servername - varchar(20)
          '$InstanceName' , -- Instance - varchar(20)
          '$KeyFileName' , -- FileName - varchar(40)
          '$KeyPassword' , -- EncryKeypwd - varchar(20)
          GETDATE(), -- Backupdate - datetime
          '$KeyFolder'  -- FileLocation - varchar(80)
        ) "
"Found Reporting Services in instance '$($InstanceName)' on $($ComputerName); will save key to '$($KeyFileName)' ..."
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close() 

}

 catch
    { 
       echo $_.Exception.GetType().FullName, $_.Exception.Message
       $From = "YourFromEmailID"
       $To = "YourToEmailID"
       $Subject = "SSRS Encryption Key Backup Failed for $computername"
       $Body = "$_.Exception.Message"
       $SMTPServer = "Your SMTP Server"
       $SMTPPort = "Your SMTP Port"
       Send-MailMessage -From $From -to $To -Subject $Subject `
       -Body $Body -SmtpServer $SMTPServer -port $SMTPPort
     }
}
}