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...
10 People are following this question.
