SQL Server Performance Forum – Threads Archive
Backup & Restore Database
Hi. I’m using SQL Server 2000.I do backup the database in the Enterprise Manager.
Is it posiible to protect the restore of backup file such as password or any encription?
My client raise the point cause if any person copy this file they can restore and see all the secure data in database.
BACKUP DATABASE { database_name | @database_name_var }
TO < backup_device > [ ,…n ]
[ WITH
[ BLOCKSIZE = { blocksize | @blocksize_variable } ]
[ [ , ] DESCRIPTION = { ‘text’ | @text_variable } ]
[ [ , ] DIFFERENTIAL ]
[ [ , ] EXPIREDATE = { date | @date_var }
| RETAINDAYS = { days | @days_var } ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] FORMAT | NOFORMAT ]
[ [ , ] { INIT | NOINIT } ]
[ [ , ] MEDIADESCRIPTION = { ‘text’ | @text_variable } ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
[ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]
[ [ , ] { NOSKIP | SKIP } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] RESTART ]
[ [ , ] STATS [ = percentage ] ]
] Using Query Analyzer, you can set password.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS†with no warranties for accuracy.
I havent tried this before. Probably this is possible if you use Windows Authentication Mode.
As suggested by Luis you can use BACKUP statement with PASSWORD option to keep a password for that file. BOL refers
PASSWORD = { password | @password_variable } Sets the password for the backup set. PASSWORD is a character string. If a password is defined for the backup set, the password must be supplied to perform any restore operation from the backup set. Important A backup set password protects the contents of the backup set from unauthorized access through SQL Server 2000 tools, but does not protect the backup set from being overwritten.
For more information about using passwords, see the Permissions section.
Also refer about RESTORE topic under BOL. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
It’s also a good idea to protect your backups by applying appropriate ACL’s on the filesystem/share where you store backups…
.. thats a good pointhttp://www.microsoft.com/sql/techinfo/administration/2000/security/securingsqlserver.mspx fyi. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Now I can set password for the backup. Thanks all for your answer.
You might want to consider using a product like Winzip to compress your file and protect it with a stronger encryption level. That backup files, even with passwords, are extremely vulnerable. We use SQL LiteSpeed to protect all our backups with very strong level encryption. Any backups we have to send off-site to third parties without LiteSpeed are sent in a password protected zip file. We use the tightest compression and highest level of encryption. The password is always sent seperately from the file. We are a financial company, so this is a big deal to us. You might want to consider the options above, depending on the sensitivity level of the data to the parties involved. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Does Microsoft support the compression of SQL backups using Winzip? I know they do not support directing backups or placing databases on a compressed drive.
Also Winzip has a 4GB file limit doesn’t it? Could get problematic for any reasonably-sized database.
Indeed – beware the 4 gig boundary, where chaos reigns <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />. Technically, WinZip 9 and higher can compress and uncompress files larger than 4 gig, and can encrypt them with AES (strong) encryption. It uses a ZIP 64 file instead of a standard zip, which is how it gets around the file size limit.<br /><br />There are other, less proprietary methods available too. We use gzip files (compression but not encryption).
]]>