How to configure Ola Hallengren backup script for encryption

backupola-hallengren

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

Best Answer

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'

Related Solutions

Sql-server – BACKUP failed to complete – with spaces, for no reason

Well, it looks like 3041 is the generic "something went boom with the backup" error.

Have you tried just running the backup SQL from SQL Agent without using the wrapping stored procedure? I'm assuming the stored procedure you're using for the backup lives in the master DB (or at least not in the DB you're backing up).

Also, you could use this query to check your backup status (last 7 days). There might be something else going on that you don't know about and that's interfering with your backups.

SELECT  
 CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
 msdb.dbo.backupset.database_name,  
 msdb.dbo.backupset.backup_start_date,  
 msdb.dbo.backupset.backup_finish_date, 
 msdb.dbo.backupset.expiration_date, 
 CASE msdb..backupset.type  
   WHEN 'D' THEN 'Database'  
   WHEN 'L' THEN 'Log'  
 END AS backup_type,  
 msdb.dbo.backupset.backup_size,  
 msdb.dbo.backupmediafamily.logical_device_name,  
 msdb.dbo.backupmediafamily.physical_device_name,   
 msdb.dbo.backupset.name AS backupset_name, 
 msdb.dbo.backupset.description 
FROM   msdb.dbo.backupmediafamily  
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
WHERE  (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)  
ORDER BY  
  msdb.dbo.backupset.database_name, 
  msdb.dbo.backupset.backup_finish_date

Sql-server – Restore a Database to a New database (T-SQL)

I believe it has something to do with inadequate disk space. Can you check your application/system event viewer logs if it's throwing an insufficient disk space error? Also figure out the mdf & ldf file sizes of the original database.

If you have created the database already on the 2nd server, check to make sure that file growth is not disabled.

One other option you can try is attaching the mdf file if you can copy that over to the restoring server and see if that works.

Best Answer

Related Solutions

Sql-server – BACKUP failed to complete – with spaces, for no reason

Sql-server – Restore a Database to a New database (T-SQL)

Related Question