Script Download: CheckEncryptionCertificates.zip
http://gallery.technet.microsoft.com/scriptcenter/Check-SQL-Server-b328b4f6

This script can be used to check your SQL Server encryption certificates without backup issue.

We can use Get-OSCEncryptionCertificates to identify the certificates used in TDE (Transparent Data Encryption) which have not been backed up and then list the affected databases. By default, this script use Windows Authentication to connect to your SQL Server. If you want to use SQL Server Authentication, just uncomment some code in this script.

 

image image
image

You can find more All-In-One Script Framework script samples at http://aka.ms/onescriptingallery