How can I add encryption to ola.hallengren backup script.

In short i want to achieve this

TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\MyTestDB.bak'  
   ALGORITHM = AES_256,  
   SERVER CERTIFICATE = MyTestDBBackupEncryptCert  
  STATS = 10  

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'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.