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!'

The BACKUP DATABASE command works fine and there is no change in the output of the command as well. Let us try to read the Header information of the backup file.

RESTORE HEADERONLY FROM DISK = 'D:EncryptionTestBackup.bak'

Since we have not provided the MediaPassword in the command, as expected it would fail.

Whoever is not aware of this password the backup file is useless for them! While using the RESTORE DATABASE including WITH MEDIAPASSWORD keyword will esnure that the Database Restore is successful.

A simple, yet very useful feature of SQL Server.

One thought on “Encrypt SQL Server Database Backups

  1. Prema

    Related notes.

    The following are deprecated features in SQL 2008.

    BACKUP {DATABASE | LOG} WITH PASSWORD
    BACKUP {DATABASE | LOG} WITH MEDIAPASSWORD
    RESTORE {DATABASE | LOG} …WITH DBO_ONLY

    So to be used cautiously.

Comments are closed.