Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cjp
Yak Posting Veteran
69 Posts |
 Posted - 2009-05-05 : 07:25:55
|
| I am using sqls 05 x64, sp 2.I would like to be able to protect sqls backups (I always generate these in the form x.bak with verification). There is no obvious way to do this without resorting to third-party applications, but the several that I have tested have all produced problems (after encryption, sqls detects the decrypted form as corrupt and refuses to use it - something to do with checksums changing).I know how to encrypt columns in a table, and have done this without problems. However, it would be easiest from a logistical standpoint if I could simply set a password to prevent a backup from being used unless the password is known. I hold the backups on usb drives so that they can be stored in a physically separate location from the installed databases.Is this possible?Thanks.Chris |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-05-05 : 09:39:52
|
MS says: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. http://msdn.microsoft.com/en-us/library/ms186865(SQL.90).aspx
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
cjp
Yak Posting Veteran
69 Posts |
Posted - 2009-05-06 : 04:06:17
|
| Thanks for this link - it has provided me with the solution, and I am now able to password backups and, as importantly, to restore them.In case other unsure people should read this thread, I think I should add (a) that the password needs to be defined explicitly and separately for the backup, for the verification and for the restore and (b) that the password option is not available from the backup wizard: it has to be scripted.Here is an example using a db called 'lookups'. The dummy password is 'password' - replace this with your own:backup with verificationBACKUP DATABASE [lookups] TO DISK = N'C:\lookups_password_test.bak' WITH NOFORMAT, INIT, MEDIAPASSWORD = 'password', NAME = N'lookups-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10GOdeclare @backupSetId as intselect @backupSetId = position from msdb..backupset where database_name=N'lookups' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'lookups' )if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''lookups'' not found.', 16, 1) endRESTORE VERIFYONLY FROM DISK = N'C:\lookups_password_test.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND, MEDIAPASSWORD = 'password'GOrestoreRESTORE DATABASE [lookups] FROM DISK = N'C:\lookups_password_test.bak' WITH FILE = 1, NOUNLOAD, STATS = 10, MEDIAPASSWORD = 'password'GOThis can also be directed to a detachable disk - fine for usb backups.Finally, Microsoft say that this facility is being removed from future releases: I understand that this procedure is present in sqls 08 but Microsoft take the view that passwording a backup is not especially safe and, therefore, that there are better ways to manage security. By the way, use 'mediapassword' rather than 'password' since this is safer.Chris |
|
|
|
|
|
|
|
|
protecting backups