Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using passwords with SQL Server database backup files


By:   |   Last Updated: 2006-11-08   |   Comments (12)   |   Related Tips: More > Backup

Problem

One issue when creating backups for SQL Server databases is that these backup files can be restored to any other SQL Server as long as the version of SQL Server supports the backup file that you are trying to restore.  In most cases this is not an issue unless you are trying to restore a backup from 6.5 or earlier onto a SQL Server 7.0 or later installation.  Because of this ability to restore the backups to any other SQL Server this exposes your data to potential theft or misuse of your data.  Most of the data in backup files is highly compressible, so a backup that maybe 1GB can easily be compressed to about 200MB.  So even your largest backup files could be vulnerable to someone copying them to their laptop or burning them to a DVD and then using the data in a way that you never intended.

Solution

One option that native SQL Server backups has is the use of password protected backup files.  When you create your backup you can specify a password for the backup file. Then when you want to restore the backup, the password must be specified otherwise the backup fails.  One problem with using this feature to protect your backups is that the GUI, both Enterprise Manger and SQL Server Management Studio do not support this option.  Also, when you create backups using maintenance plans this is not a supported option either.  So the only way to create password protected backups is by using the T-SQL commands for both backup and restore functions.

To do this it is very straight forward, here is the T-SQL command to backup your database:

BACKUP DATABASE Northwind TO DISK='C:\Northwind.BAK' WITH MEDIAPASSWORD='mssqltips'

Once the backup has been create we can run restore commands to see that the file can not be accessed without the password.

RESTORE FILELISTONLY FROM DISK='C:\Northwind.BAK'

restore filelist

RESTORE HEADERONLY FROM DISK='C:\Northwind.BAK'

restore headeronly
RESTORE VERIFYONLY FROM DISK='C:\Northwind.BAK'

verify database

RESTORE DATABASE Northwind FROM DISK='C:\Northwind.BAK'

restore database

If we specify the password for any of these commands the commands work without issue.

RESTORE FILELISTONLY FROM DISK='C:\Northwind.BAK' WITH MEDIAPASSWORD='mssqltips' 
RESTORE HEADERONLY FROM DISK='C:\Northwind.BAK' WITH MEDIAPASSWORD='mssqltips' 
RESTORE VERIFYONLY FROM DISK='C:\Northwind.BAK' WITH MEDIAPASSWORD='mssqltips'
RESTORE DATABASE Northwind FROM DISK='C:\Northwind.BAK' WITH MEDIAPASSWORD='mssqltips'

When trying to use the GUI to do a restore, if the backup is password protected you will get these error messages:

SQL Server 2000

restore database

SQL Server 2005, 2008, 2008R2

restore database

Take a look at using passwords to protect your backup files. Although it does not offer the highest degree of security it does at least offer one additional hurdle someone will need to overcome in order to do the restore.

Note: 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.

Next Steps
  • Although this does add a level of security if someone really wants to crack the passwords they will find a way, so look for additional ways to secure your data
  • You should make sure your backup files are not in a location that is easily accessible to people that should not have access to these files
  • Make sure that people do not have the ability to create backup files if they are not supposed to.  The password option has be issued, so if someone can create a backup without the password and they can easily do a restore too.
  • Keep your passwords in a secure place.  If you don't remember the password you will not be able to restore the backup file.


Last Updated: 2006-11-08


get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, April 07, 2017 - 9:53:35 AM - paragk Back To Top

Thanks greg!
Most important part for me was It is not supported MSSQL-2014 onwards.

 


Wednesday, November 12, 2014 - 7:41:31 AM - Greg Robidoux Back To Top

Thiruppathi, what version of SQL Server are you using and what error message do you get?

It looks like this feature was discontinued with SQL Server 2012. This is from the Microsoft website

"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."


Wednesday, November 12, 2014 - 3:14:23 AM - Thiruppathi Back To Top

I FOLLW THE ABOVE INSTRUCTION BUT

I CAN'T ABLE  TO RESTORE THE  PASSWORD BAKUP FILE


Thursday, June 19, 2014 - 9:09:11 AM - Greg Robidoux Back To Top

Hi Shiba,

not exactly sure what you are asking.  If you are asking about files that sit in the file system you would need to see if there is a tool that does this at that level.  If you are talking about documents that are stored in a SQL Server database take a look at this post about encrypting blob data: http://sqlblog.com/blogs/michael_coles/archive/2009/04/08/let-s-encrypt-a-blob.aspx

Thanks
Greg


Thursday, June 19, 2014 - 4:57:16 AM - shiba Back To Top

Is it possible to password protected  file (csv,txt,excel etc) using ms slq.If possible please help.


Saturday, April 27, 2013 - 2:19:58 AM - Sunil Kumar Back To Top

what is the code or script for making a sql server 2008 database password ptotected..??

Please give me the detailed solution.

Thank You.

 


Thursday, March 07, 2013 - 1:19:04 PM - Greg Robidoux Back To Top

Hi Georgina, your command should look like this:

RESTORE DATABASE TestPassword FROM DISK='C:\DATABASE1-2013-03-06 18-00-01.BAK'
WITH MEDIAPASSWORD='somepassword',
MOVE 'GUIDB' to 'C:\PasswordTest\GUIDB.mdf',
MOVE 'GUIDB_log' to 'C:\PasswordTest\GUIDB_1.ldf',
RECOVERY


Thursday, March 07, 2013 - 12:09:14 PM - Georgina Back To Top

Hi Greg,

Thanks for your tips on database backups and restores.

I have configured a database backup with a password on a server (Server A) and need to test the restore on a different server (Server B).
I've copied the backup (DATABASE1-2013-03-06 18-00-01.BAK) across to Server B to the location C:\PasswordTest

Server A  - the mdf and ldf files are on E:\ and F:\
Server B  - only has a C:\


Basically I need to know how to do a restore through code containing both parameters WITH MEDIAPASSWORD and WITH MOVE.


My code is the following:

RESTORE DATABASE TestPassword FROM DISK='C:\DATABASE1-2013-03-06 18-00-01.BAK' WITH MEDIAPASSWORD='somepassword'
WITH MOVE 'GUIDB' to 'C:\PasswordTest\GUIDB.mdf',
MOVE 'GUIDB_log' to 'C:\PasswordTest\GUIDB_1.ldf',
RECOVERY


I've tried various syntax but keep gettin the following error:

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'WITH'.
Msg 319, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.


Is it possible to do a database restore to a different drive using WITH MEDIAPASSWORD together WITH MOVE?

 

many thanks,

Georgina


Thursday, February 28, 2013 - 5:12:11 PM - Greg Robidoux Back To Top

@lahib - there is both a PASSWORD and MEDIAPASSWORD option.  Please refer to this article for more details: http://msdn.microsoft.com/en-us/library/ms186865(v=sql.105).aspx

Also, what error are you getting?


Thursday, February 28, 2013 - 4:55:54 PM - lahib Back To Top

 

I have SQL 2008 R2. The previous SQL command will not work with the password. Any ideas????

Thanks

Lahib


Friday, December 28, 2012 - 2:47:52 PM - Ashish Malwal Back To Top

Hi  i have some backup dumps with password protected when i am trying to load in to my dtabase in sql2005 it is showing acess deined due to password failure.there is no chance of getting me password can help me out through this problem .how can i get it done sucess with out pass word.Pls Give me solution



Error is:-  Msg 3279, Level 16, State 4, Line 1

Access is denied due to a password failure

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

 

Wednesday, September 05, 2012 - 10:35:48 AM - sandeep Back To Top

hai i ahve some backup dumps with password protected when i am trying to load in to my dtabase in sql2005 it is showing acess deined due to password failure.there is no chance of getting me password can help me out through this problem .how can i get it done sucess with out pass word.


Learn more about SQL Server tools