December 28, 2011 at 9:06 am
I want to protect with password my Database Backup when try to Restore the database in sql server 2008.
December 28, 2011 at 9:17 am
Per BOL:
--Backup Set Options
COPY_ONLY
| { COMPRESSION | NO_COMPRESSION }
| DESCRIPTION = { 'text' | @text_variable }
| NAME = { backup_set_name | @backup_set_name_var }
| PASSWORD = { password | @password_variable }
| { EXPIREDATE = { 'date' | @date_var }
| RETAINDAYS = { days | @days_var } }
If a password is defined for the backup set, the password must be supplied to perform any SQL Server restore operation from the backup set. A backup set password does not protect the backup file from being overwritten, however.
The protection provided by this password is weak. It is intended to prevent an incorrect restore using SQL Server tools by authorized or unauthorized users. It does not prevent the reading of the backup data by other means or the replacement of the password. The best practice for protecting backups is to store backup tapes in a secure location or back up to disk files that are protected by adequate access control lists (ACLs). The ACLs should be set on the directory root under which backups are created.
For More: http://msdn.microsoft.com/en-us/library/ms186865.aspx
January 7, 2012 at 7:18 am
If you're on SQL 2008 Enterprise edition, look into TDE (transparent database encryption). That will prevent anyone from restoring the database to a server that doesn't have the appropriate certificates. If you go that route, make sure you backup the certificates carefully.
Otherwise consider a 3rd party backup product that encrypts database backups (many of them do)
The password feature on backups is no more than a weak deterrent at best and should not be considered proper protection of a backup file (and neither should mediapassword). It's also deprecated and will be removed in a future version
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply