Windows Azure SQL Database Backup and Restore
A backup and restore strategy is a necessary to protect against data loss. The built-in fault tolerance capabilities of Windows Azure protect your data from individual server, network, and device failures. However, in order to protect your data against user or application errors, or a total loss of a region, you must create your own backup of the data.
Recommended Backup Strategy
Creating a database copy and then exporting a BACPAC file gives you a transactionally consistent backup file of the database that is portable and can be stored in Windows Azure storage or on-premises locations. This file then can be used for a rollback plan to protect against user or application errors, or in the event of loss of the entire region.
Recommended tools for Backup
Database Copy: The database copy feature creates a new database that is transactionally consistent with the source database at the time when the copy process is completed. What this means is that when the copy process is in progress, any changes that were made to the source are replicated to the copy. The copy can be created on the same server as the source or on a different server. The copy can be used in the rollback plan for changes to schema or data, or unwanted deletions or modifications. You can automate backup operations and specify the frequency and retention period for exported databases. For more information, see How to: Import and Export a Database (Windows Azure SQL Database).
SQL Database Import Export Service: The SQL Database Import Export Service copies the object definitions and data from the source Windows Azure SQL Database to a logical export file (BACPAC). This operation is followed by a bulk copy of the data from the user tables to the BACPAC file. The file can be either stored in the Windows Azure Blob storage service, or downloaded to an on-premises location as protection against widespread region failures.
|You are charged for each database copy. To avoid additional charges for the copies, you can set the database as read-only, and export a BACPAC file directly from the database to create a transactionally consistent copy. However marking the database as read-only locks the database access until the export is complete and the read-only settings are reverted.|
SQLPackage.exe (Data-Tier Application client tool) can be used to export or import a BACPAC file. For more information, see SqlPackage.exe.
|SQL Data Sync should not be used as part of your backup strategy as there are several limitations. It does not version, it only backs up data and no other objects. For more information, see the SQL Data Sync FAQ topic.|
Restoring a Windows Azure Database
Restore from a database copy: When the copy is on the same server as the source database, to restore, you can rename the copy to that of the source database. If the copy is on a different server, you can point your application to the copy.
Restore from a BACPAC file: Using the SQL Database Import/Export Service, import a BACPAC file to create a new database. Point your application to the new database. Or alternatively, you can rename the source and the new database.
Automation and Scheduling Considerations:
Backing up a Windows Azure SQL Database involves copying and exporting a BACPAC from the copy. To automate the process, you can use settings in the SQL Database Export Service to specify the frequency of backups and the retention period for exported databases. You can also create a program or script. The script or program should first copy the database, monitor the copy process, and when the copy is complete, export a BACPAC file from the copy. You can schedule the program to run on a recurring basis by using a task scheduler like a Windows Scheduler or by using with Window Azure worker role.
|The scheduling frequency allows for submitting a job, but cannot be used to determine the RPO (Recovery Point Objective). For example, scheduling a copy or export of a database every hour does not guarantee that the RPO is an hour. The size of the database and the load on the servers can affect the job completion time.|
Consider the following recommendations when you are creating the program or script:
To avoid failure caused by certain errors such as timeouts, include exception handling and retry logic. For more details, see Retry Logic for Transient Failures in Windows Azure SQL Database.
To make sure that the retry logic does not retry indefinitely, and to avoid throttling, the retry logic should retry for a set number of times, spacing out each retry with different time intervals.
Automating the export operation from the Windows Azure Management Portal: This method allows you to configure export settings – subscription name, storage account, export frequency, start date, and the retention period – to schedule an automated export operation. You can also perform a one-time export operation, or create a new SQL database from an existing export file. For more information, see How to: Import and Export a Database (Windows Azure SQL Database).
Creating a script for copying a Windows Azure SQL Database: Transact-SQL scripts can be used to perform a database copy and monitor the copying process. For sample scripts, see Copying Databases in Windows Azure SQL Database.
Creating a program for exporting a BACPAC file: The following are three methods to export a Windows Azure SQL Database:
Call the Import/Export Service via REST Endpoints:. This method starts the Import/Export Service programmatically and triggers an export from Windows Azure to Azure blob storage. To download a sample code, see Windows Azure SQL Database Import/Export Sample Application.
Using the Data-Tier Application Framework (DACFx) client tool - sqlpackage.exe: This method exports the database from Windows Azure to a local disk. Sample syntax for export:
"C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe" /a:Export /ssn:”yourserver.database.windows.net” /sdn:"your database to export" /su:"yourdbuser" /sp:"your password" /tf:"bacpac file to create on local disk"
Using the Data-Tier Application Framework (DACFx) public API: This method exports the database from Azure to local disk. You can also export the file to Windows Azure Blob storage by using Windows Azure API. To download a sample code, see DAC Framework Direct Community Sample Application.
Links to Content and Videos Related to Windows Azure SQL Database Backup and Restore
.This video demonstrate the two step process of creating a transactionally .
This topic introduces the Database copy feature, concepts, and considerations for same server and cross server copying.
This topic is a step-by-step walkthrough from creating a copy of a SQL Database, to monitoring its completion.
This topic is a step-by step walkthrough of exporting a BACPAC file, and importing a BACPAC file to create a new Windows Azure SQL Database.
This sample application illustrates how to use the Windows Azure SQL Database Import/Export service to export a Windows Azure SQL Database as a BACPAC, and then import that same BACPAC from Windows Azure Blob storage into a new Windows Azure SQL Database.
This topic explains business continuity concepts in depth, and includes detailed information on the built-in fault tolerance capability in Windows Azure. This article has detailed information on business continuity for the following three categories: