Encrypt SQL Server Database Backups

Most of my posts are either inspired by some real life issues that I face at work or by some of the questions that I respond to in the online communities. Today’s post is based on one of the questions that I had responded to last week.

The user has a SQL Server instance’s database backups configured to be taken to disk and from there some third party application takes a copy of it on to the Tapes. His question was whether the backup files that goes into the tape can be encrypted that too without making any modifications in the third party tape backup software.

Why not? SQL Server has this option even in SQL Server 2000. This is one of the less used features of SQL Server. All it takes is to include the WITH MEDIAPASSWORD keyword in the BACKUP DATABASE command. This option ensures that the BACKUP SET is encrypted for any read operation but it does not stop the Backup File from getting overwritten.  Wherever this Backup File is referred to, the MEDIAPASSWORD has to be provided in the command.

Let me explain this with an example. I have created a database called EncryptionTest and I am taking the FULL Database backup with the MEDIAPASSWORD keyword.

BACKUP DATABASE EncrptionTest TO DISK = 'D:\EncryptionTestBackup.bak'
WITH INIT, MEDIAPASSWORD ='StrongPassword!'

Read more: SQL Server Pedia

Posted via email from .NET Info