Encrypt Database Backups in SQL Server Using MEDIAPASSWORD Option


April 25, 2012
Page: 1/2

Introduction

One of the primary job responsibilities of a database administrator is to make sure all the production databases are backed up regularly. However, one must be well aware of the fact that the database backup file when it is not password protected or encrypted can be very easily restored on to a SQL Server Instance either of the same version or on to a higher version. Since database can be easily restored on to another instance there is a huge potential for data thief or misuse. In this article we will take a look at why it is important for a database administrator to take password protected or encrypt database backups in SQL Server. The steps mentioned in this article are applicable to SQL Server 2000 and higher versions. Let us go through a real world example to understand how to take database backups using Password Protected option there by encrypting database backup file.

"This article has a total of 2 pages including this page. Click the links below to open all 2 pages in a seperate window to learn How to Encrypt Database Backups in SQL Server. Page 1, Page 2"

Password Protected Database Backup Script for SQL Server

If you carefully go through the below script you would notice that we have included MEDIAPASSWORD option within the database backups command and we have also specified the password which will be used to Protect the database backup file. You could see that it is very simple to Password Protect a Database Backup file.

In the below sample script we have also used the COMPRESSION parameter; by using COMPRESSION parameter one can take a compressed backups for a database this will help you reduce database backup space and reduces time to back up a database. To know more about Compression feature which was introduced in SQL Server 2008 read the article titled “Database Backup Compression Feature in SQL Server 2008

BACKUP DATABASE AdventureWorksDW 
TO DISK = 'C:\DBBackups\AdventureWorksDW.BAK' 
WITH 
COMPRESSION, 
MEDIAPASSWORD='MyTechMantra.com'
GO

Now that we have Password Protected Database Backups File with us let us now go ahead and try restoring the database using the below database restore script.

Restore Database Script for SQL Server

RESTORE DATABASE AdventureWorksDW
FROM DISK = 'C:\DBBackups\AdventureWorksDW.BAK'

Msg 3279, Level 16, State 6, Line 1
Access is denied due to a password failure
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

The database restore script failed with the following “Access is denied due to a password failure” error message


RESTORE FILELISTONLY Command

Restore FILELISTONLY command retuns a result set containing list of all the database and log files contained within the database backup set. You could see below that the RESTORE FILELISTONLY command failed because Password to read the encrypted backup file was not specified.

RESTORE FILELISTONLY 
FROM DISK = 'C:\DBBackups\AdventureWorksDW.BAK' 

Msg 3279, Level 16, State 6, Line 1
Access is denied due to a password failure
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.


RESTORE HEADERONLY Command

Restore HEADERONLY command retuns a result set containing all database backup header information for all backup sets on a particular backup device. You could see below that the RESTORE HEADERONLY command failed because Password to read the encrypted backup file was not specified.

RESTORE HEADERONLY 
FROM DISK = 'C:\DBBackups\AdventureWorksDW.BAK' 

Msg 3279, Level 16, State 6, Line 1
Access is denied due to a password failure
Msg 3013, Level 16, State 1, Line 1
RESTORE HEADERONLY is terminating abnormally.


RESTORE VERIFYONLY Command

Restore VERIFYONLY command verifies the database backup file and checks to see that the backup set is complete and the entire database backup set is readable when required. This command when used will not restore the database however, it just verifies the structure of the data contained within the backup files. You could see below that the RESTORE VERIFYONLY command failed because Password to read the encrypted backup file was not specified.

RESTORE VERIFYONLY 
FROM DISK = 'C:\DBBackups\AdventureWorksDW.BAK' 

Msg 3279, Level 16, State 6, Line 1
Access is denied due to a password failure
Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.

Click on Next Page to continue reading rest of the article…

Next Page..

Continue Free Learning...

  • Please leave below your valuable feedback for this article.
  • Feel Free to refer this article to your friends and colleagues using the below “Share this Article” option.
  • Do subscriber to our News Letter to continue your free learning.
  • Don’t forget to Like Us on Facebook and do follow us on Twitter for latest updates.

Share this Article





Follow @MyTechMantra on Twitter
We're on Facebook
Bookmark and Share

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."