Author – Pratibha Beniwal, Associate Data Engineer
Introduction –
SQL Server backups are an essential part of every good disaster recovery strategy and setting up such backups to run effortlessly should be the goal. In this article, we will demonstrate how to create Full, Differential and Transaction log backups and how to schedule automated SQL Server backups into Azure Storage Account.
Backup
It is a copy of data/database, etc. Backing up the MS SQL Server database is essential for protecting data. MS SQL Server backups are mainly three types − Full or Database, Differential or Incremental, and Transaction Log or Log.
Full. This is the most common backup type and it includes everything including objects, system tables data, and transactions that occur during the backup. With a full backup, you can restore your database to the state, when it was backed up. Full backups won’t truncate your transaction log but if your database is in full recovery you should also consider transaction log backups.
Differential. This type of backup offers a means to maintain a complete history of your database but without storing redundant data. A differential backup retains data since the last full backup. A differential backup is only useful if used in tandem with a full backup, but allows you to delete/remove previous differential backups as they are redundant.
Transaction log. This backup type will backup all of the transactions that have occurred since the last log backup or truncation, then it will truncate the transaction log. This will capture all transaction information, both DML and DDL, that has occurred on the database. With a transaction log backup, you can restore a database to a particular point in time aka point-in-time recovery, like right before a data loss event.
Prerequisites
- Azure Storage Account
- sysadmin role on the login role
- Sample AdventureWorksLT2016 Database
- SQL Server Any Edition and SSMS
Steps to set up the environment –
Step 1 — Create an Azure Storage Account and three Containers in it.
Step 2 — Connect your SQL Server with SSMS.
Step 3 — Restore the sample AdventureworksLT2016 database.
Step 4 — Right-click on ‘AdventureWorksLT2016’ database and select tasks. Click Backup and the following screen will appear. Select the backup type (Full\diff\log) and in ‘back up to’ click on URL.
NOTE: Same steps will be performed for differential and transaction log backup.
Step 5 — Add the destination path where the backup file will be created:
Add -> Select a subscription -> Select Storage Account -> Select Blob Container -> Create Credentials.
Step 6 — Click OK to create ‘AdventureWorksLT2016’ database full backup as shown in the following snapshot.
Step 7 — Create new Credentials in the Security section.
Expand Security -> Right Click on Credentials and select New Credential.
Step 8.1 — Write Azure Storage Account Name in Identity text box. Paste Storage Account Access Keys in Password
and then click ok.
Step 8.2 — Copy Storage Account key from Storage Account -> Access Keys.
Step 9 — Expand and Right Click Management and open Management Plan Wizard.
Step 10 — Add the details and select the Scheduling Time. I have scheduled it to run this Job every 1 hour, daily.
Step 11 — Select the Database Backup Type.
Step 12 — Select the required Database name and Backup to the URL option.
Step 13 — Select the SQL Credentials which we have created in Step 8. Write Storage Container name in the next field. Verify the URL Prefix and backup extension type.
Note:- In transaction Log Backup, the Backup file extension will be ‘.trn’.
Step 14 — Click on Next button and Finish the Management Wizard.
Step 15 — Expand Maintenance Plan and you can see a backup job is created.
Right Click on the Job and click on execute.
Step 16 — After the job runs successfully, click on close button.
Step 17 — You can check the backup files that will be created in storage account.
Step 17.1 — As I have scheduled, full backup files will be created every hour.
Step 17.2 — As I have scheduled, differential backup files will be created every 15 min.
Step 17.3 — As I have scheduled, transaction log backup files will be created every 5 min.
