How to Create password protected Backup of database In SQL Server 2008.

SQL Server supports password protection for backup media and backup sets. Beginning with SQL Server 2012 the PASSWORD and MEDIAPASSWORD options are discontinued for creating backups. You can still restore backups created with passwords.

Let see how to create a database backup with password:

Create A Sample database 'Secure_Bakup' using below Script:

USE master
GO
CREATE DATABASE SECURE_BAKUP
GO
USE SECURE_BAKUP
GO
CREATE TABLE T1(ID INT ,NAME CHAR(30));
GO
INSERT INTO T1 VALUES(1,'ABC'),(2,'BCD'),(3,'TYS')
GO

we can see the records from Table T1 as below:

SELECT * FROM T1

Create the database backup as below:

BACKUP DATABASE SECURE_BAKUP TO 
DISK='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\SECURE_BAKUP.BAK' 
WITH PASSWORD='ABC@123' 

Lets verify the backup set without password:

RESTORE VERIFYONLY FROM  
DISK='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\SECURE_BAKUP.BAK'

You will get authentication error as below:

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

Lets try to restore the backup set without password:

RESTORE DATABASE [SECURE_BACKUP2] FROM  
DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\SECURE_BAKUP.BAK' 
WITH  FILE = 1,  
MOVE N'SECURE_BAKUP' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SECURE_BACKUP2.mdf',  
MOVE N'SECURE_BAKUP_log' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SECURE_BACKUP2_1.LDF',  
STATS = 10
GO

You will get authentication error as below:

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

Now lets verify the same backup set with password:

RESTORE VERIFYONLY FROM  
DISK='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\SECURE_BAKUP.BAK' 
WITH PASSWORD='ABC@123'
GO

Its successful. "The backup set on file 1 is valid."

Now lets restore the backup set with password:

RESTORE DATABASE [SECURE_BACKUP2] FROM  
DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\SECURE_BAKUP.BAK' 
WITH  FILE = 1,  
MOVE N'SECURE_BAKUP' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SECURE_BACKUP2.mdf',  
MOVE N'SECURE_BAKUP_log' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SECURE_BACKUP2_1.LDF',  
STATS = 10,
PASSWORD='ABC@123'
GO

And the database is successfully restored. Below is the output for same:

12 percent processed.
21 percent processed.
30 percent processed.
43 percent processed.
51 percent processed.
60 percent processed.
73 percent processed.
81 percent processed.
90 percent processed.
100 percent processed.
Processed 184 pages for database 'SECURE_BACKUP2', file 'SECURE_BAKUP' on file 1.
Processed 2 pages for database 'SECURE_BACKUP2', file 'SECURE_BAKUP_log' on file 1.
RESTORE DATABASE successfully processed 186 pages in 0.246 seconds (5.903 MB/sec).

So this is how we can create password protected backup in SQL Server 2008. 
Please make sure that you are using SQL Server 2008 as these features(Password and MEDIAPASSWORD) are discontinued from SQL Server 2012.

Thanks For Reading this Post!!!