Backup

Backup and Restore functionality is affected by DbDefence.

How DbDefence protects database backups

  • All backups are entirely encrypted, even if you use masking and mask a single column in a single table.
  • WITH COMPRESSION option works for encrypted backups.
  • Unmasked values never appear in the backup.
  • For special occasions, you may create an unencrypted backup.

RESTORE Requirements:

  • To restore an encrypted backup, you need to install DbDefence.
  • You can restore the backup only to the database encrypted with the same password and algorithm.
  • To restore the encrypted backup, you need to create an empty database, then encrypt it with the same password as the source database. Important: If the original database has several data or log files, the new database must have the same files.
  • If the database size exceeds license limitations, you will not be able to restore and will receive an error.
  • Any user with appropriate SQL permissions can do backup and restore.

By default any backup produced from an encrypted database is automatically encrypted. These backups are not system dependent and so they can be freely copied to another server or computer. Restoring encrypted data to the database encrypted with the same password runs as usual.

Redgate Backup, Lightspeed backup and others

DbDefence works with 3rd party backup tools in a single-threaded mode.

DbDefence, unlike TDE, can compress encrypted data. So, if SQL Server edition supports compression, switch to Native Compression. Example of Redgate Backup settings:

Steps to restore an encrypted backup on any server.

1. Install DbDefence on the server.

2. If you use a licensed version of DbDefence, apply a license. Licensed users may get a 3-day activation keys instantly from here.

3. Create an empty database (Example: CREATE DATABASE MYDB).

4. If you used a certificate for encryption, install the certificate and select it.

5. Encrypt the database with the password as the source database.

6. Restore the backup as usual. Don't forget to set "Overwrite database" option.

overwrite database

If you see this ...

Encrypted backup restore error

... then you're restoring an encrypted backup without the password. An encrypted backup can be restored only to the database encrypted with the same password and algorithm as the source database! So, to solve the problem, simply create a database, encrypt it with the same password, and restore the backup to this database. Of course, DbDefence must be installed on this server.

Licensed users may instantly get a 3-day activation key for emergent cases here if they need to restore a large database. Alternatively, an unencrypted backup can be generated.

Unencrypted backup

You may produce an unencrypted backup of an encrypted database.

use mydatabase
open symmetric key dbdx decryption by password='MyPassword'
exec dbd_backup_option @backupmode=0
backup database mydatabase TO DISK = 'mydatabase.bak'

Calling dbd_backup_option changes the backup mode only in the current session. You can save that mode permanently:

use mydatabase
open symmetric key dbdx decryption by password='MyPassword'
exec dbd_backup_option @backupmode=0, @save=1

To revert to the encrypted backup mode:

use mydatabase
open symmetric key dbdx decryption by password='MyPassword'
exec dbd_backup_option @backupmode=-1, @save=1

How to see that the backup is encrypted

Limitations

To verify backup consistency with VERIFYONLY, you need to have the attached database encrypted with the same password.