Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
You can encrypt your backups with SQL Server’s own encryption method in order to avoid the risk of unauthorized access to the backup.
You must first create a master key on the instance. You can create it as follows.
1 2 3 | USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'your_password'; |
You must then create a certificate. You can create it as follows.
1 2 3 4 | Use Master GO CREATE CERTIFICATE MyCert WITH SUBJECT = 'My Test Certification'; |
After you create your certificate, your first job is to get the backup of your certificate. You can perform this operation with the help of the following script.
1 2 3 | BACKUP CERTIFICATE MyCert TO FILE = 'C:\Backup\MyCert' WITH PRIVATE KEY ( FILE = 'C:\Backup\MyCertPrivateKey' , ENCRYPTION BY PASSWORD = 'your_password' ); |
After you have backed up your certificate, you can get an encrypted backup of your database with the help of the script to your share named \\Myshare
1 2 3 4 5 6 7 8 9 10 11 | BACKUP DATABASE [MyDB] TO DISK = N'\\MyShare\MyDB.BAK' WITH COMPRESSION, ENCRYPTION ( ALGORITHM = AES_256, SERVER CERTIFICATE = MyCert ), STATS = 1 GO |
To restore Encrypted Backup to another instance, you can use the article “How To Restore Encrypted Backup To Another Instance On SQL Server“.
