3

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

In short i want to achieve this

BACKUP DATABASE [MyTestDB]  
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\MyTestDB.bak'  
WITH  
  COMPRESSION,  
  ENCRYPTION   
   (  
   ALGORITHM = AES_256,  
   SERVER CERTIFICATE = MyTestDBBackupEncryptCert  
   ),  
  STATS = 10  
Go

1 Answer 1

5

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'
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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