TO DISK = N'C:\Backup\AdventureWorks2008R2.bak' The normal BACKUP DATABASE (or BACKUP LOG) command is used, simply supplying the WITH ENCRYPTION parameter as shown in Listing 4. Essentially, this means that the backup must be written to a new backup file each time a backup is taken.Įncrypting a database when it is being backed up is very similar to a traditional SQL Server backup. When using backup encryption with SQL Server backups, each backup must be written to a new media set. With the CPU power available in modern SQL Servers and the amount of CPU power available to people who may attempt to break the encryption, selecting AES 256 is strongly encouraged. The stronger the encryption that is used, the more CPU power required to encrypt the data, and the more CPU power required for someone to attempt to break the encryption. These are shown below, in order of strength: When backing up the database you can select from four different encryption keys. Once the certificate or asymmetric key has been created, the database can be backed up using the certificate or asymmetric key, to secure the backup. If the row does not exist, then create a master key within the master database by using the CREATE MASTER KEY command as shown in Listing 1.įigure 2: Enable the backup encryption by checking “Encrypt backup” Backing up the Database If the row exists, then there is nothing else which needs to be done. Whether a master key has been installed can be verified by querying the _keys catalog view and looking for a key named #MS_DatabaseMasterKey#. ![]() ![]() ![]() Odds are that there is already a master key within the master database, as SQL Server will put one there by default when SQL Server is installed. The first thing to check is that the master database has a master key in the database. Preparing the Instance for Encrypted Backupsīefore you can have the SQL Server database engine encrypt your backups, you have some basic setup which needs to be done. With the release of Microsoft SQL Server 2014, we have the first version of SQL Server that supports encrypting database backups directly from the database engine without any third party software being installed on the SQL Server.
0 Comments
Leave a Reply. |