0

I have to take an unencrypted full backup of a database so that it can be sent over to other team after which they can restore it in their own environment. The other team has a different environment and a different SQL Server certificate.

My server setting for a full backup is taking an AES 128 encrypted backup. And I don't intend to share the decryption key and certificate with the other team. My version of SQL Server is Standard 2014 SP2.

Encrypted Full backup

I have seen encrypting the backup but this is something I haven't come across yet. Has anybody encountered this type of scenario before. Any help will be greatly appreciated.

EDIT -

In my case, all SQL server database backups are encrypted using a self signed certificate. This certificate is installed on all servers so only a single certificate needs to be managed. In the event the certificate expires it will need to be renewed as backups will not run with an expired certificate. Additionally, it is required to have the certificate and private key loaded to restore backups created with the key pair.

Installed certificates along with expiration date

It was this certificate and private key which I do not intend to share with the other team. Hence, require a copy-only full backup that can bypass the certificate encryption. Is there any way, I can get through the above scenario by creating a backup which doesn't require certificate and private key to be loaded.

Adding the output of the query as asked by learning-dbadmin -

enter image description here

Improve this question
7
  • Sorry I did not got your question. Were you trying to add a picture ?, I cannot see the pic
    – Shanky
    Commented Jan 9, 2020 at 9:36
  • @Shanky - Are you able to see the picture now. I have added it again.
    – Vivek Kumar Singh
    Commented Jan 9, 2020 at 9:40
  • Why do you think your backups are encrypted? The option is disabled
    – Yannick Liekens
    Commented Jan 9, 2020 at 9:43
  • Could you please include output of below command in your question --> SELECT encryption_state, key_algorithm, key_length FROM sys.dm_database_encryption_keys
    – Learning_DBAdmin
    Commented Jan 9, 2020 at 11:24
  • @Learning_DBAdmin - Added the query output. No rows returned.
    – Vivek Kumar Singh
    Commented Jan 9, 2020 at 11:54

1 Answer 1

Reset to default
6

By default SQL will not encrypt backups. From what I see in your screenshot, the backup you're taking there, will not be encrypted either. (The option is just disabled)

A simple way would be to just use T-SQL to create your backup. ( Note copy_only unless you want to be starting a new backup chain) (and replace the folder with your backup dir)

BACKUP DATABASE DBase TO DISK = '\\Folder\DBase_20200109_1034.bak' WITH COPY_ONLY,STATS = 5

Check BOL if you need more parameters like compression. Aslong as you don't add the encryption clause, your backup will remain non-encrypted.

Of course, feel free to use the GUI as well, aslong as you don't set "Back up to a new media set, ...." and click to encrypt your backups they should all be non-encrypted

Improve this answer
2
  • Thank you for answering the first part, which I fully understand now. Have you ever come across self signed certificates which encrypt the backup.
    – Vivek Kumar Singh
    Commented Jan 9, 2020 at 11:21
  • 1
    If database is having TDE encryption then, I don't think you can take a backup without encryption.
    – Learning_DBAdmin
    Commented Jan 9, 2020 at 11:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.