How to Password Protected Backup In SQL Server

How to Password Protected Backup In SQL Server

In this post we will learn How to Password Protected Backup In SQL Server. Password Protected backup in SQL Server gives you an additional security, to password protect SQL Server files we will use MEDIA PASSWORD. Setting backup media password are important and useful for securing misuse of data. If database backup is password protected then one will not be able to restore.

In earlier post we had discussed How To Delete Backup Files Older Than n Days, Creating Resource Pools in SQL Server and Resource Governor in SQL Server.

Note: Media Password only works with T-SQL, GUI do not have any option for password, for backup and restore a password protected file we have to use T-SQL.

Password Protected Backup In SQL Server Query:

----BACKUP DATABASE WITH MEDIA PASSWORD----

BACKUP DATABASE ADVENTUREWORKS2008R2
TO DISK='A:\ADVETUREWORKS2008R2.BAK' 
WITH MEDIAPASSWORD='PASSWORD'

----RESTORE DATABASE WITH MEDIA PASSWORD-----

RESTORE DATABASE ADVENTUREWORKS2008R2
FROM DISK='A:\ADVETUREWORKS2008R2.BAK' 
WITH MEDIAPASSWORD='PASSWORD'

Error when trying to restore backup without password: