This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am showing MEDIAPASSWORD option to set a password to your database backup file in SQL Server.
We should not take a risk with our backup media file, especially when we are taking backup of sensitive data like the banking system.
You can set a password using MEDIAPASSWORD option while taking a backup, and you cannot restore that backup file without specifying a password.
You can set MEDIAPASSWORD in plain text, so again you have to take care of your backup/restore scripts. So again you have to take care of your backup/restore scripts.
Once you set a password, it applies to all types of restore option like RESTORE FILELISTONLY, RESTORE HEADERONLY, RESTORE LABELONLY, RESTORE VERIFYONLY, and you must require a password for all these options.
Beginning with SQL Server 2012 the PASSWORD and MEDIAPASSWORD options are discontinued for creating backups. It is still possible to restore backups created with passwords.
Let me demonstrate this,
T-SQL Script to take a backup with MEDIAPASSWORD:
I set password ‘dbrnd.com’ for my dbrnd database backup.
|
1 2 3 4 5 |
BACKUP DATABASE dbrnd TO DISK = 'C:\MyBackups\dbrnd.bak' WITH COMPRESSION, MEDIAPASSWORD='dbrnd.com' GO |
|
1 2 3 |
RESTORE DATABASE dbrnd FROM DISK = 'C:\MyBackups\dbrnd.bak' GO |
|
1 2 3 4 |
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. |
|
1 2 |
RESTORE HEADERONLY FROM DISK = 'C:\MyBackups\dbrnd.bak' |
|
1 2 3 4 |
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. |
|
1 2 3 4 5 |
RESTORE DATABASE dbrnd FROM DISK = 'C:\MyBackups\dbrnd.bak' MEDIAPASSWORD = 'dbrnd.com', RECOVERY GO |
