4. Backups

Database backups are among the most important tasks a database administrator must perform. A good database backup strategy can help recovery from system outages, unwanted deletions or updates, database corruption issues, and other related issues.

This chapter will help you back up an Azure SQL Database and an Azure SQL Managed Instance. You'll learn about automated and manual backups, explore automated backup features, and perform the manual backup of an Azure SQL Managed Instance.

This chapter explores different backup and restore options, such as automated backups, transactional consistent backups, and manual backups. We will be covering the following topics:

• Automatic backups
• Optimizing backup storage cost
• Configuring long-term backup retention for Azure SQL Database and Azure SQL Managed Instance
• Exporting an Azure SQL database using the Azure portal
• Exporting an Azure SQL database using PowerShell
• Performing a manual COPY_ONLY...

Automatic backups

Microsoft provides automated backups for Azure SQL Database and Azure SQL Managed Instance databases. Automatic backups consist of full, differential, and log backups. The first automatic full backup is performed immediately after the database is provisioned. Differential backups are scheduled to occur every 12-24 hours, and transaction log backups are scheduled for every 5-10 minutes. The frequency of transaction log backups is based on the compute size and the amount of database activity. A full backup is scheduled for once a week:

Figure 4.1: Automatic backups

Note

Differential and transaction log backups can run in parallel.

Backup storage

Azure SQL Database and SQL Managed Instance keep database backups in geo-redundant storage blobs by default, which are replicated to a paired region. This helps to recover the database in a different region if there is a regional outage in the primary region. You can choose between locally...

Optimize backup storage costs for Azure SQL Database and Azure SQL Managed Instance

Backup storage costs depend on the provisioned database pricing tier and type. For the DTU model, the Azure SQL Database backup storage cost is included in the pricing tier. With the vCore model, Microsoft gives you free backup storage, which is equal to the size of your maximum provisioned database storage or reserved maximum storage for Azure SQL Managed Instance. For example, if you have a 100 GB standard Azure SQL database, you get 100 GB of free backup storage, and the same applies to the reserved total space for Azure SQL Managed Instance. You can control the free backup storage size by limiting the retention period of backups.

For single databases, this equation is used to calculate the total billable backup storage usage:

Total billable backup storage size = (size of full backups + size of differential backups + size of log backups) - maximum data storage

For pooled databases, the total...

Activity: Configure LTR Backups for Azure SQL Managed Instance using PowerShell

Let's get back to our example of ToyStore Ltd. Mike has been tasked with ensuring that a LTR policy is configured for the newly deployed toystore database on Azure SQL Managed Instance. Since this feature is new and only available using PowerShell, let's see various commands to manage LTR on Azure SQL Managed Instance.

In this activity, Mike first decides to create an LTR policy for 6 weeks of full backup retention. Then he will be changing it to 12 weeks and will retain the Week 16 backup for 5 years using LTR. Later, he needs to ensure that the newly configured LTR policy and backup retention is properly configured. At last, he will make sure that restoring from LTR backups is working.

Perform the following steps to complete the activity:

1. Let's start with creating an LTR policy.

   Create an LTR policy, setting up variables as per our environment and subscription:

   # Get the SQL...

Manual Backups for Azure SQL Database

Conventional database backup statements don't work in Azure SQL Database. A manual backup consists of exporting the database as a DACPAC (schema) or BACPAC (schema + data) and bcp (bulk copy program utility) out the data into CSV files.

Manual backups can be performed in the following ways:

• Exporting a BACPAC to your Azure storage account using the Azure portal
• Exporting a BACPAC to your Azure storage account using PowerShell
• Exporting a BACPAC using SQL Server Management Studio (SSMS)
• Exporting a BACPAC or a DACPAC to an on-premises system using sqlpackage.exe

DACPAC and BACPAC

DACPAC stands for Data-Tier Application Package and contains the database schema in .xml format. A BACPAC is a DACPAC with data.

DAC is a database life cycle management tool that simplifies the development, deployment, and management of data tier elements supporting an application.

A BACPAC is generally used to move a database...

Activity: Perform Manual Backups Using PowerShell

Let's get back to our example of ToyStore Ltd. Mike has been tasked with ensuring that all the data of ToyStore Ltd. is backed up for crises such as system outages, unwanted deletions or updates, database corruption issues, and other related issues. In order to automate this process, he wants to make use of PowerShell scripts. In this activity, we'll learn how to back up an Azure SQL Database using PowerShell scripts:

Note

You need an Azure storage account to carry out the activity. If you don't have an existing storage account, you can create one by running the following command:

New-AzStorageAccount -ResourceGroupName myresourcegroup '
-Name mystorageaccountname '
-SkuName Standard_LRS '
-Location 'East US' '
-Kind StorageV2 '
-AccessTier Hot

It's advised to create the storage account at the same location as the Azure SQL Database. This minimizes the network delay...

Summary

Azure SQL Database backups are different from on-premises database backups. The regular backup database command isn't supported in Azure SQL Database. In this chapter, we have learned about the automatic backups that are unique to Azure SQL Database and Azure SQL Managed Instance and that aren't available in an on-premises database.

We also learned about automatic backup frequency and backup storage, as well as multiple options for optimizing backup storage costs for Azure SQL Database and Azure SQL Managed Instance.

We also learned how to configure LTR backups for Azure SQL Database and Azure SQL Managed Instance, and discussed how to take manual backups of an Azure SQL database using SSMS and PowerShell, and native manual COPY_ONLY backups using T-SQL for Azure SQL Managed Instance.

In the next chapter, we will look at the restore options available for Azure SQL Database and Azure SQL Managed Instance.