Hi all,

is there a way with SQL 2005 to protect backup files.?

 Thks

Hi Akhtar,

Their are some third party tool which you can use to compress and encrypt your database backup files, also you can password protect your backup files. Apart from this what you can do is, always keep your db backup files in protected folder and maintain proper authentication so that only those who really need to have access can access backup files. 

thanks for the reply hemant, but can we password protect a backup file with SQL Backup uiltity, or is there any tweak or work around to do this without using any 3rd party tool

You can zip the the back up file with password..but i dont know how muchit worth it , you can use the following code for that.

   DECLARE @PASSNUMBER INT
   DECLARE @PASSNUMBERSTRING CHAR(8)
   DECLARE @PASS CHAR(8)
  
   SELECT @PASSNUMBER = CONVERT(INT,(RAND()* 1000000))
   SELECT @PASSNUMBERSTRING = CONVERT(CHAR,@PASSNUMBER)
   SELECT @PASS='-S'+CONVERT(CHAR,@PASSNUMBER)

   SET @CMD = '"C:\PROGRAM FILES\WINZIP\WZZIP" '
  + @ZIPFILEPATH
  + '   '
  + @OUTPUTFILEPATH
  +'  '
  + @PASS
   EXEC @RESULT = MASTER.DBO.XP_CMDSHELL @CMD, NO_OUTPUT

Thanks, but not the ideal solution, as will have to delete the bak file somehow after the zip!!!

You may need to lookat Data Protection Manager - http://blogs.technet.com/dpm/archive/2007/07/02/backup-here-comes-protection.aspx from Microsoft, see if it helps.