Backup

Backup and Restore is affected by DbDefence.

Important things:

  • Any user with appropriate SQL permissions can do backup and restore.
  • You can't restore encrypted backup to unencrypted database.
  • You can't restore encrypted backup to database encrypted with different password.
  • To restore encrypted backup you need to have DbDefence installed.
  • To restore encrypted backup you need to create an empty database and encrypt with the same password as source database. Important: if original database has several data or log files the new database must have the same files.
  • If database size exceeds license limitation you will not be able to restore and receive errors about checksums error.

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 usually.

Unencrypted backup

You may produce unencrypted backup of encrypted database.

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

Calling dbd_backup_option changes 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 encrypted backup mode:

exec dbd_backup_option @backupmode=-1, @save=1

Limitations

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