Azure SQL Database Backup and Restore: A Comprehensive Guide

Backing up your Azure SQL Database is crucial to prevent data loss in case of a disaster or human error. You can use the built-in backup feature in Azure SQL Database to create a full or differential backup of your database.

Azure SQL Database offers three types of backups: automatic, manual, and point-in-time recovery. Automatic backups are enabled by default and run daily, while manual backups can be scheduled or run on demand. Point-in-time recovery allows you to restore your database to a specific point in time, up to 35 days ago.

To restore your Azure SQL Database, you can use the Azure portal, Azure CLI, or PowerShell. The restore process creates a new database with the same schema and data as the backed-up database. You can also use the Azure SQL Database restore feature to restore a database from a backup file.

Azure SQL Database backup and restore is a critical aspect of maintaining data integrity and ensuring business continuity. It's a process that helps protect your data from corruption or deletion, and enables database restore to a point in time within the configured retention period.

Database backups are an essential part of any business continuity and disaster recovery strategy, because they help protect your data from corruption or deletion. This is especially important in case of malware incidents or inexplicable data corruption.

With Azure SQL Database, you can configure long-term retention (LTR) for both single and pooled databases, making your backups available for an extended time (up to 10 years). This is a feature that can be particularly useful for organizations that need to comply with strict data retention requirements.

The Azure administrator dashboard provides you with the tools to create a backup, and Azure installs an agent on the SQL server so that the backup service can discover it and create backups automatically. This process is two steps: the discovery process displays all databases on your cloud network, and you choose the databases that you want to back up.

To manually create a backup, you can go to your Azure portal and type "backup center" to open the Backups dashboard. From there, you can configure the backup frequency, retention timeframe, and the items that you want to back up.

Azure SQL Database uses SQL Server engine technology to back up and restore data for service tiers other than Hyperscale. For Hyperscale databases, backup and restore are based on storage snapshots, which provide instant backup and fast restore capabilities irrespective of database size.

Here are the types of backups you can configure in Azure SQL Database:

By understanding how Azure SQL Database backup and restore works, you can ensure that your data is protected and your business is prepared for any unexpected events.

You can use Azure SQL database backups to restore your database to a point in time in the past within the retention period. This can be done through the Azure portal, Azure PowerShell, the Azure CLI, or the REST API.

Backup options include restoring an existing database, restoring a deleted database, restoring a database to another geographic region, and restoring a database from a long-term backup.

Here are the specific backup options:

Automated database backups are also available, which can be configured to store backups for up to 35 days. However, if you need backups for an extended time, you can configure long-term retention policies.

Automated database backups are a lifesaver in case of data corruption or deletion. They're automatically managed by Microsoft for Azure SQL Managed Instance.

With automated backups, you can restore your database to a specific point in time within a configured retention period, up to 35 days. This is a huge relief in case of unexpected data loss.

You can even configure long-term retention (LTR) policies per each database if your data protection rules require it. This allows you to store backups for an extended time, up to 10 years.

Automated backups are created and stored on Microsoft-managed Azure storage accounts, so you don't have to lift a finger.

Backup is a crucial aspect of data protection, and Azure SQL Database offers various options to ensure your data is safe.

You can use Azure portal, Azure PowerShell, the Azure CLI, or the REST API to restore an existing database to a point in time in the past within the retention period.

Azure Backup is a service that allows you to create backups of your databases, and it's automatically enabled when you create a VM. You can also manually create a backup by going to the Backups dashboard in the Azure portal and clicking the New button.

To create a backup, you need to configure the backup frequency, retention timeframe, and the items you want to back up. You can choose to back up hourly, daily, or weekly, and set a retention period of up to 35 days.

Here are some ways you can use Azure Backup:

You can also use SQL Server Management Studio (SSMS) to export a database to your local storage location. This will create a full backup in a .bak file format that you can use to restore your database if needed.

With Azure SQL Managed Instance, SQL Server database engine backups are automatically managed by Microsoft and stored on Microsoft-managed Azure storage accounts. This means you don't have to worry about manual backups, but you can still configure long-term retention (LTR) policies per each database if needed.

Exporting a database is a viable alternative to automatic backups. Automatic backups taken by the Azure service are not available to download or access directly, they can only be used for restore operations through Azure.

You can export the database schema and data to a BACPAC file, which is a ZIP file containing the metadata and data from the database. A BACPAC file can be stored in Azure Blob storage or in local storage in an on-premises location.

The export procedure produces a BACPAC file that you can download locally and later import back to Azure or even into a SQL Server on-premises installation. This allows for flexibility in data management.

To guarantee transaction consistency, a copy of your Azure Database will be created during the export process, which is treated as a second database and is billed as such. This can result in significant time and money spent on exporting a large database.

To add data to your database, you'll need to follow a few simple steps. Go to Settings and select SQL databases, then sql-my-db.

First, you'll need to log in to your database. In the left menu, select Query editor (preview), and log in with the dbadmin credentials.

To ensure you have data for validating backup and restore processes, create a table. This will give you a solid foundation for testing and verifying your backup and restore processes.

Here are some key topics related to backup and recovery: AWS Backup & RecoveryN2WS vs AWS BackupAWS Cost OptimizationAWS Data Lifecycle ManagementAzure / Multi-Cloud BackupAmazon EFSAmazon FSxSAP HANA Backup

Azure SQL Database automatically manages backups, including full, differential, and transaction log backups. The exact frequency of these backups varies, but typically includes full backups every week, differential backups every 12 or 24 hours, and transaction log backups approximately every 10 minutes.

The frequency of transaction log backups depends on the compute size and the amount of database activity, so it may vary. You can't change the schedule of backup jobs or disable them, as the SQL Database service balances the overall system workload to determine the exact timing.

Here's a summary of the typical backup frequency:

Note that this schedule can be adjusted based on the workload, and the system aims to complete full backups within a designated window. However, if necessary, the backup may continue beyond the scheduled time until it completes.

Full backups are typically scheduled to occur every week, with the exact timing determined by the SQL Database service.

The frequency of differential backups varies, but they are usually taken every 12 hours in some cases, and every 24 hours in others.

Transaction log backups are taken approximately every 10 minutes, although this can vary depending on the compute size and amount of database activity.

You can't change the schedule of backup jobs or disable them, as the exact timing of all database backups is determined by the SQL Database service as it balances the overall system workload.

Here are the typical frequencies of backups:

In some cases, transaction log backups may be taken more frequently, depending on the specific requirements of your database.

Short-term retention is a crucial aspect of database management. It's the default configuration for all new, restored, and copied databases, retaining sufficient backups to allow point-in-time recovery (PITR) within the last 7 days.

Differential backups can be configured to occur either once in 12 hours or once in 24 hours. A 24-hour differential backup frequency might increase the time required to restore the database, compared to the 12-hour frequency.

In the vCore model, the default frequency for differential backups is once in 12 hours. In the DTU model, the default frequency is once in 24 hours.

You can change the backup retention period for each active database in the range of 1 to 35 days, except for Basic databases, which are configurable from 1 to 7 days.

If you delete a database, the system keeps backups in the same way for an online database with its specific retention period. You can't change the backup retention period for a deleted database.

Here's a summary of short-term retention options:

If you need to keep backups for longer than the maximum short-term retention period of 35 days, you can enable long-term retention.

Azure SQL Database offers both short-term and long-term retention of backups, allowing for Point-in-Time Recovery within the retention period and meeting various compliance requirements.

You can configure full long-term retention backups for up to 10 years in Azure Blob Storage, with the frequency depending on the policy. For example, setting W=0, M=1 would create an LTR copy monthly.

Azure Policy can be used to enforce backup storage redundancy, ensuring that databases are created with the correct storage redundancy settings. You can assign policies to a subscription using the Azure portal or Azure PowerShell.

To enable data residency requirements, you can use the BACKUP_STORAGE_REDUNDANCY parameter in the CREATE DATABASE statement, specifying LOCAL or ZONE as input.

Here are the built-in policy definitions for SQL Database, which can be reviewed for more information:

Retention is a crucial aspect of database management, ensuring that your data is safe and compliant with regulatory requirements. You can configure short-term retention in Azure SQL Database for Point-in-Time Recovery (PITR) within a specified retention period.

Azure SQL Database allows you to configure full long-term retention (LTR) backups for up to 10 years in Azure Blob Storage. This is particularly useful for meeting various compliance requirements.

To meet different compliance requirements, you can select different retention periods for weekly, monthly, and/or yearly full backups. The frequency depends on the policy, and you can use the LTR pricing calculator to estimate the cost of LTR storage.

You can configure long-term retention for Hyperscale databases created or migrated from other service tiers. However, if you attempt to enable LTR for a Hyperscale database where it isn't yet supported, you'll receive an error message.

Here are the possible retention periods for weekly, monthly, and yearly full backups:

To configure the backup retention policy for your database, follow these steps: navigate to the Azure portal, select the logical server, and configure the retention policy for the database.

You can enforce data residency requirements by using Azure Policy to require zone-redundant or locally redundant backups for your SQL database. Azure Policy is a service that helps you keep your Azure resources compliant with your corporate standards and service-level agreements.

To enforce data residency requirements at an organizational level, you can assign policies to a subscription by using the Azure portal or Azure PowerShell.

If you enable the policy "Azure SQL DB should avoid using GRS backup", databases cannot be created with the default storage as globally redundant storage. This policy prevents users from using GRS with the error message "Configuring backup storage account type to 'Standard_RAGRS' failed during Database create or update."

Azure policies are not enforced when you're creating a database via T-SQL. To specify data residency when you're creating a database by using T-SQL, use LOCAL or ZONE as input to the BACKUP_STORAGE_REDUNDANCY parameter in the CREATE DATABASE statement.

Here are some built-in policy definitions for SQL Database that you can review for a full list:

You can also use Azure Policy to enforce backup storage redundancy for your SQL managed instance. This helps you keep all your data in a single Azure region, meeting your data residency requirements.

Azure Policy is not the only way to enforce redundancy policy. You can also configure a backup retention policy for your database to ensure that your backups are stored redundantly. To configure a backup retention policy, navigate to the Retention policies tab and click on the database you want to configure.

The backup and restore process for Azure SQL Database is a crucial aspect of data management. You can use these backups to restore an existing database to a point in time in the past within the retention period.

To manually create a backup, go to your Azure portal and type “backup center” to open the Backups dashboard. Click the New button to start the backup process.

The discovery process displays all databases on your cloud network, and you can choose the databases that you want to back up. You can also configure the backup frequency, retention timeframe, and the items that you want to back up.

To restore your database, go to your Azure portal and search for “backup center” to open it. In the Backup Center, click the Restore tab. Choose the datasource type, which would be Azure virtual machines or serverless SQL Server instances.

You can restore a database to a specific time by choosing the restore point in the next window. For serverless databases, type “sql instances” in the Azure portal and open it. Click the database instance that you want to restore, and click the Restore tab in the dashboard.

If you're using the Azure backup service, the entire VM or database instance will be backed up and can be restored to a specific time of your choice. Serverless SQL Server instances can also be restored to a specific time.

To restore a database from a long-term backup, users specify the backup’s timestamp through Azure’s management tools, initiating the creation of a new database instance based on this historical data.

Here are the steps to restore a database from a long-term backup:

The restore operation creates a new instance of the database at the specified point in time, ensuring no impact on the current live database while enabling thorough verification of recovered data. Once validated, this newly restored database can replace the original one or be used to retrieve items of data.

Azure SQL Database backup and restore are designed with security and compliance in mind. Data recovery policies are preserved during migrations to ensure uninterrupted data availability.

PITR retention periods can be adjusted to meet specific compliance requirements. You can change the PITR backup retention period to meet your needs. For more information, see Change the PITR backup retention period.

The Change automated backup settings article provides steps to delete personal data from devices or services, supporting obligations under the GDPR. Microsoft's GDPR section on the Trust Center and Service Trust portal offer general information about GDPR compliance.

Azure SQL Database automatically encrypts backups at rest, including LTR backups, if your database is encrypted with TDE. All new databases in Azure SQL are configured with TDE enabled by default.

Azure SQL databases are configured with Transparent Data Encryption (TDE) enabled by default, which automatically encrypts backups at rest.

All new databases in Azure SQL are configured with TDE enabled by default, making it easier to ensure the security of your data.

If your database is encrypted with TDE, backups are automatically encrypted at rest, including Long-Term Retention (LTR) backups.

Microsoft is fully responsible for keeping and rotating keys for databases with service-managed keys (SMK), which means you don't have to worry about managing encryption keys.

Automatic backups stored in Azure-managed storage accounts are automatically encrypted by Azure storage, providing an additional layer of security for your data.

Integrity is a top priority in Azure SQL, with automated tests run on a daily basis to ensure the integrity of database backups.

The Azure SQL engineering team automatically tests the restore of automated database backups, which also receive DBCC CHECKDB integrity checks upon point-in-time restore.

Any issues found during an integrity check trigger an alert to the engineering team, who can quickly address the problem.

All database backups are taken with the CHECKSUM option to provide additional backup integrity, giving you peace of mind in case of data loss.

Compliance is a top priority for any business, and it's great to know that Azure provides tools to help you meet your compliance requirements.

You can change the PITR retention period if the default retention doesn't meet your compliance needs. For more information, see Change the PITR backup retention period.

The Change automated backup settings article provides steps on how to delete personal data from the device or service, which can be used to support your obligations under the GDPR.

The GDPR section of the Microsoft Trust Center and the GDPR section of the Service Trust portal are great resources to learn more about GDPR and how to comply with it.