asked
Create backup with MEDIAPASSWORD
Can anyone tell me whether using the MEDIAPASSWORD parameter in a backup actually encrypts the data in the backup file or simply stops a restore taking place unless the password is supplied please? Have read this:
http://msdn.microsoft.com/en-us/library/aa225964(v=SQL.80).aspx and dont see a clear declaration of situation...
I don't know for sure, but I think that this will be just a password to allow restore/backup to the mediaset. Microsoft never offered an encryption option for backups (except for TDE but that isn't just for backups), so I would be *very* surprised if this option did anything to encrypt data. It'd certainly be a feature that I'd be screaming about if I were Mirosoft - it'd also be a direct competition against SQLBackup's encryption abilities.
There are no built in encryption options for backing up SQL databases, you have to go with 3rd party utilities for that functionality (TDE aside which is database level encryption meaning that your database is encrypted thus the backup will be as well). All that option does is require you to enter a password to backup or restore a particular media set, that does not prevent the backup from being readable or being able to be accessed by some other utility than SQL Server.
The reason for it existing in the first place - no idea, however it has been depcreated and removed from SQL Server with the 2012 release (you can still restore databases with MEDIAPASSWORD however can't use that option with your backup any longer).
http://msdn.microsoft.com/en-us/library/ms186865.aspx There are some really good 3rd party options, but your wanting to mask some data might be a better use case for column level encryption.
@SirSQL - Possibly being a bit stupid here but what business case does the MEDIAPASSWORD solve then? Simply preventing a backup being accidentally restored? This is purely for curiosity, I have no issue to resolve...
I do know a product that does backup encryption in case anyone is interested.
This whole thread has come about as I simply wanted to mask some data ( a few sensitive columns) out of a backup for a test database that we are parking development on.!
the situation is resolved, the DB is only 50MB when backed up so its backed up and zipped with a password. I was only curious as to how the MEDIAPASSWORD worked and wheat it was for. Grant is teasing me, he knows I use SQL Backup and have the encryption option in place on my important databases. Redgate also do a Data Masker tool -
http://www.red-gate.com/labs/sql-data-masker/
10 People are following this question.
