Creating a compressed back-up of a database

Since SQL2014 (Standard edition and higher), SQL has an integrated back-up the functionality.

Pro’s to create your back-ups like this are:

• Backing up is faster
• Less IO is needed
• The .BAK files will be smaller
• No extra .ZIP step needed to get an encrypted database

The script below is an example of how to back-up ,encrypt and compress a database. The script will take the following steps:

1. Create master key and certificate on the production server (frequency: once)
2. Back up certificate (frequency: once)
3. Back up and compress your database, and encrypt the result (frequency: periodically)
4. Create a master key with the restored certificate on the remote server (frequency: once)
5. Restore the database on remote server (this is only possible on a server where the certificate is present, see step).

– Create (one-time) masterkey and certificate on local Server
USE MASTER;
GO
– create master key and certificate
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘YOURPASSWORD’;
GO
CREATE CERTIFICATE YOURCERTIFICATENAME
WITH SUBJECT = ‘YOURCERTIFICATENAME Certificate’, EXPIRY_DATE = ‘yyyymmdd’;
GO
– Export the backup certificate to a file in order to be able to restore database on remote server
BACKUP CERTIFICATE YOURCERTIFICATENAME TO FILE = ‘Z:\Backup\YOURCERTIFICATENAME.cert’
WITH PRIVATE KEY (
FILE = ‘z:\Backup\YOURCERTIFICATENAME.key’,
ENCRYPTION BY PASSWORD = ‘YOURPASSWORD’)

– If needed, drop masterkey and certificate (on local and/or remote serer)
– DROP MASTER KEY
– In case masterkey already has been created to encrypt a certificate, first drop certificate
– DROP CERTIFICATE YOURCERTIFICATENAME

– Backup database using encryption/certificate and compression
BACKUP DATABASE [MA!N DatabaseName]
TO DISK = N’Z:\Backup\MAIN_DatabaseName.bak’
WITH
COMPRESSION,
ENCRYPTION
(
ALGORITHM = AES_256,
SERVER CERTIFICATE = YOURCERTIFICATENAME
),
STATS = 10
GO

– On Remote Server
– Recreate master key and certificate
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘YOURPASSWORD’;
GO
– restore the certificate
CREATE CERTIFICATE YOURCERTIFICATENAME
FROM FILE = ‘Z:\Backup\YOURCERTIFICATENAME.cert’
WITH PRIVATE KEY (FILE = ‘Z:\Backup\YOURCERTIFICATENAME.key’,
DECRYPTION BY PASSWORD = ‘YOURPASSWORD’);
GO

– After the procedure above it should be possible to restore the database BAK on the remote server.