SQL Server Backup with Password

Kailash Chandra Behera | Sunday, June 14, 2020

Introduction

My previous blog demonstrated how to back up SQL server database using SQL Server Management Studio. Here we will be demonstrated how to create SQL Server Backup with Password using SSMS.

Getting Started

Here the demonstration is performed in the SQL Server Management Studio 2017, it backups a 'Test' database which is created for demp purpose only.

The 'Test' database will be backed up with encryption to the default backup location. Follow the below steps to backup SQL Database.

Steps to Create SQL Server Backup with Password

  1. Open SQL Server Management Studio and connect to the appropriate SQL instance

  2. After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, expand the server tree.

  3. Expand Databases, expand System Databases, right-click master, and click New Query to open a query window with a connection to your Test database.

  4. Execute the following commands to create a database master key and a certificate within the master database.

     -- Create the master key  
     CREATE MASTER KEY ENCRYPTION BY PASSWORD = '123#K456#C789B';   
     -- If the master key already exists, open it in the same session that you create the certificate (see next step)  
     OPEN MASTER KEY DECRYPTION BY PASSWORD = '123#K456#C789B';  
     -- Create the certificate encrypted by the master key  
     CREATE CERTIFICATE MyCertificate  
     WITH SUBJECT = 'Backup Cert', EXPIRY_DATE = '20201230';  
    
  5. In Object Explorer, in the Databases node, right-click Test, point to Tasks, and then click Back Up....

  6. On the Media Options page, in the Overwrite media section select Back up to a new media set, and erase all existing backup sets.

  7. On the Backup Options page in the Encryption section select the Encrypt backup check box.

  8. From the Algorithm drop-down list, select AES 256.
  9. From the Certificate or Asymmetric key drop-down list select MyCertificate.
  10. Select OK.
password protect sql backup

SQL Server Backup with Password

Related Articles

  1. Restore Database to Microsoft Azure Blog
  2. Backup SQL Database to the Azure Blob
  3. Backup SQL Database Using SSMS
  4. Enable SQL Remote Connection
  5. Encrypt data across communication channels
  6. Recovery Database From Warm-Standy Mode

Summary

Here in the above demonstration, we learn how to create SQL Server Backup with Password. I hope you have enjoyed it a lot.

Thanks