Using passwords with SQL Server database backup files

By:   |   Updated: 2006-11-08   |   Comments (12)   |   Related: > 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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2006-11-08

Comments For This Article




Friday, April 7, 2017 - 9:53:35 AM - paragk Back To Top (54408)

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 (35265)

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 (35261)

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 (32312)

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 (32306)

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 (23591)

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 7, 2013 - 1:19:04 PM - Greg Robidoux Back To Top (22636)

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 7, 2013 - 12:09:14 PM - Georgina Back To Top (22635)

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 (22491)

@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 (22490)

 

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 (21180)

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 5, 2012 - 10:35:48 AM - sandeep Back To Top (19397)

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.














get free sql tips
agree to terms