KB #240046: Backup not encrypted even though “encrypt new” flag was set

Type:

Tip

Summary:

This article explains why a SQL Backup is not being encrypted, even though the “encrypt new” flag is set, and the Backup is being created WITH INIT.

Additional Information:

The “Encrypt New Backups” flag (set in Admin Wizard – Additional Options screen; or programmatically the BLDCMD CLI) directs NetLib to encrypt new databases and backups when the file is being created. However, if an existing backup file already exists, SQL reuses that file even if you specify “WITH INIT”. So since a new backup file is not being created, it remains unencrypted. herefore, in order to encrypt the backup you can do one of two things:

• If you have turned on the “encrypt new” flag and you will not be appending to an existing backup set:
  

  Delete the physical file. For example, the following query will delete the file associated with the device: mybackup1

  declare @filename as varchar(255), @command as varchar(255)
  set @filename = 
        (select phyname from master.dbo.sysdevices where name = 'mybackup1')
  if @filename is not null begin
     set @command = 'del "' + @filename + '"'
     EXEC master..xp_cmdshell @command , no_output
  end
  

• If you will be appending to an existing backup set, or you are not using the “Encrypt New Backups” flag:
  

  Use Encrypt/Decrypt Wizard (or the SECTOOL CLI) to encrypt the existing backup file. Once the original backup file is encryped, all subsequent writes to the file will remain encrypted. Remember, it must be encrypted with the same key (or one of the same keys) specified in Admin Wizard  or with the BLDCMD API.

Related Topics:

240038: SQL database backups to a Mapped Drive are not encrypted