Encrypting a SQL Server database backup is necessary in many cases, especially when the database has sensitive data.
SQL Server provides an easy way to encrypt database backups.
Let’s further examine this functionality with a step-by-step example.
In this example, we are going to backup a SQL Server 2014 database, encrypt it, and then restore it on a SQL Server 2016 instance. The sample database’s name is “TestDB1” (not quite an original name for a database 🙂
In SQL Server Management Studio, if we right-click on the database and go to “Tasks”, “Back Up…”, we are presented with the well-known backup dialog:
Learn more tips like this! Get the Online Course!
Check our online course titled “Essential SQL Server Administration Tips” (special limited-time discount included in link).
Learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!
--Create Database Master Key and Encrypt it with a Strong Password USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyComplexMasterKeyPassword'; GO --Create Backup Certificate USE master; GO CREATE CERTIFICATE TestDB1BackupEncryptCert WITH SUBJECT = 'TestDB1 Backup Encryption Certificate'; GO --IMPORTANT NOTE: It is critical that you backup the master DB key and the database backup certificate to a secure location --Backup Master DB Key BACKUP MASTER KEY TO FILE = 'c:\tmp\MasterKey.key' ENCRYPTION BY PASSWORD = 'S3curePass!'; GO --Export the Backup Certificate to a File BACKUP CERTIFICATE TestDB1BackupEncryptCert TO FILE = 'c:\tmp\TestDB1Cert.cert' WITH PRIVATE KEY ( FILE = 'c:\tmp\TestDB1CertKey', ENCRYPTION BY PASSWORD = 'S3curePassCert!')
Note that the above file keys are created by the service account that runs SQL Server Database Engine and it is the only user that has full access. In order to get access to these files, if you are a local administrator on the machine running SQL Server, you can do so by editing the permissions (via Advanced dialog).
Now, let’s try again to take an encrypted backup of the database:
--Recreate master DB key on destination SQL Server instance CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'S3curePass!'; GO --Restore the Certificate Based on the Previously Exported Key/Cert files CREATE CERTIFICATE TestDB1BackupEncryptCert FROM FILE = 'c:\tmpBackups\keys\TestDB1Cert.cert' WITH PRIVATE KEY (FILE = 'c:\tmpBackups\keys\TestDB1CertKey', DECRYPTION BY PASSWORD = 'S3curePassCert!'); GO --Restore Encrypted Database 'TestDB1' RESTORE DATABASE [TestDB1] FROM DISK = 'c:\tmpBackups\TestDB1.bak' WITH MOVE 'TestDB1' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\TestDB1_Data.mdf', MOVE 'TestDB1_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\TestDB1_Log.ldf'; GO
As you can see, now the encrypted database has been successfully restored on the destination SQL Server instance:
Featured Online Courses:
- Essential SQL Server Administration Tips (new)
- The Philosophy and Fundamentals of Computer Programming (new)
- Introduction to Azure SQL Database
- SQL Server 2019: What’s New
- Entity Framework: Getting Started (Ultimate Beginners Guide)
- SQL Server Fundamentals (SQL Database for Beginners)
- How to Import and Export Data in SQL Server
- Get Started with SQL Server in 30 Minutes
Other SQL Server Security-Related Articles
- How to Enable SSL Certificate-Based Encryption on a SQL Server Failover Cluster
- Why You Need to Secure Your SQL Server Instances
- [DBNETLIB] [ConnectionOpen (SECDoClientHandshake()).] SSL Security Error – How to Resolve
- Should Windows “Built-In\Administrators” Group be SQL Server SysAdmins?
- SQL Server Row Level Security by Example
- Frequent Password Expiration: Time to Revise it?
- Policy-Based Management in SQL Server
- The “Public” Database Role in SQL Server
- Encrypting SQL Server Databases
- Transparent Data Encryption (TDE) in SQL Server
- Encrypting a SQL Server Database Backup
Check our latest software releases!
Easily generate snippets with Snippets Generator!
Secure your databases using DBA Security Advisor!
Convert static T-SQL to dynamic and vice versa with Dynamic SQL Generator.
Rate this article:
Reference: SQLNetHub.com (https://www.sqlnethub.com)