In the documentation for Ola Hallengren's backup scripts, he explains these parameters:
- @Encrypt Y/N - Y means encrypt the backup, N is the default
- @EncryptionAlgorithm - sounds like you're looking for AES_256, which is a valid option that he supports
- @ServerCertificate - so you can specify the one you want to use
At the bottom of the page, in the examples, there's an example to back up all user databases with compression, encryption, and a server certificate:
EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@Compress = 'Y',
@Encrypt = 'Y',
@EncryptionAlgorithm = 'AES_256',
@ServerCertificate = 'MyCertificate'
So based on my extensive research and a good thirty seconds of reading, it would be:
EXECUTE dbo.DatabaseBackup @Databases = 'MyTestDB',
@Directory = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\',
@BackupType = 'FULL',
@Compress = 'Y',
@Encrypt = 'Y',
@EncryptionAlgorithm = 'AES_256',
@ServerCertificate = 'MyTestDBBackupEncryptCert'