[Quick Start Guide] Backup Azure SQL Server Database

Why backup Azure SQL server database

Azure is Microsoft's public cloud computing platform that provides a range of cloud services. With Azure subscription, you can create and run SQL databases in the cloud with simple operations in Azure portal.

Database data is an important asset that needs to be protected by backups. Unlike local SQL Server, you don’t have to worry about hardware failures on Azure. However, even in the cloud, there may be application errors, or accidental insertion or deletion of data. Therefore, backup is still important on Azure.

Fortunately, Azure always backs up all Azure SQL Server databases automatically. Let’s see its backup options.

How does Azure SQL Server database automatically backed up

Backup type: When you deployed a SQL database, Azure will perform a full backup immediately. After that, every week, Azure automatically creates full backups of your SQL Server databases, differential backups every 12–24 hours, and transaction log backups every 5–10 minutes according to the compute size and the changes you made. So you don't need to manually back up SQL Server database in Azure.

Backup storage redundancy: By default, the backups will be stored in geo-redundant storage blobs that are replicated to a paired region. With different costs, you can also change the backup storage redundancy to local redundant backup storage, or zone redundant backup storage, which keeps all the backup copies locally in the same data center, or in different availability zones in the same region.

Backup retention policy: Azure supports point-in-time-restore (PITR) and Long-term retention SQL database backup retention policies. PITR allows you to keep the backups for 7–35 days, and LTR allows you to keep weekly/monthly/annual backups up to 10 years.

How to configure backup retention time of Azure SQL Server databases

1. Access to Azure portal. Select All services and search for SQL Server, select the target SQL Server.

2. In the left inventory, select Backups option under Settings. And click Retention policies on the right page.

3. Select the target database in the list below, and click Configure policies.

4. Configure the backup retention time in the pop-up window. As you can see, you can choose how long would you like point-in-time-restore (PITR) backups to be kept, and how many weeks/months/years you want to keep your long-term retention (LTR) backups.

5. Click Apply to confirm the settings.

How to backup Azure SQL database to local machine

1. To backup Azure SQL Server database, first access to Azure portal. Select the SQL database you want to back up. Select Overview in the left inventory and click Set server firewall on the right page.

2. Click Add client IP to add a new rule, and then click Save.

3. Back to Overview tab, click on the Server name to copy it.

4. Launch SQL Server Management Studio (SSMS) and connect to your Azure SQL database using the copied Server name.

5. Right-click on the target database name and select Tasks > Export Data-tier Application… And click Next in the pop-up wizard.

6. On the Export Settings page, select Save to local disk option, and click Browse… to select a storage path. Then click Next.

7. Review all the configurations on the Summary page, and click Next to create the .bacpac backup file, which including the data, schemas, tables, references and all other database components. Then you can import it to another SQL server on your local machine.

Summary

Running SQL Server databases in the cloud can avoid data loss caused by hardware failures, but there may still have risks of application crashes or mis-operations. Therefore, there are still needs to back up Azure SQL Server database, and sometimes export to local storage.

In this article, I introduced how to change the default Azure SQL database backup policy, and how to export it to local machine. Hope this could be helpful to you.