Knowledge Base

Search Knowledge Base

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” flag (set in Admin Wizard – Additional Options screen; programmatically with build3, or fn_n_encodenew) 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.Therefore, 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” flag:

    Use Encrypt/Decrypt Wizard (or the fn_n_encodefile API)to encrypt the existing backup file. In this case, it will remain encrypted. Remember, it must be encrypted with the same key (or one of the same keys) specified in Admin Wizard (secadmin.exe) or with the fn_n_setkey API.

Related Topics:

240038SQL database backups to a Mapped Drive are not encrypted

Top