SQL Server Backup with Password

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

Open SQL Server Management Studio and connect to the appropriate SQL instance
After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, expand the server tree.
Expand Databases, expand System Databases, right-click master, and click New Query to open a query window with a connection to your Test database.

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';

In Object Explorer, in the Databases node, right-click Test, point to Tasks, and then click Back Up....
On the Media Options page, in the Overwrite media section select Back up to a new media set, and erase all existing backup sets.
On the Backup Options page in the Encryption section select the Encrypt backup check box.
From the Algorithm drop-down list, select AES 256.
From the Certificate or Asymmetric key drop-down list select MyCertificate.
Select OK.