Having the possibility to backup your SQL Server databases to Windows’s cloud platform is very convenient, easy, and takes a great weight off your organization’s shoulders but there are a few things that improve this process.

Just by adopting a few good practices in your work with Azure, you can improve the entire flow of backing up and restoring your databases.

Starting with the basics, a backup and restore strategy is essential to protect yourself against data loss. Although Azure’s high availability, built-in fault tolerance, and level of security are most reassuring and protect you from individual server, network, or device failures, the best way to protect your data is to also make your own backup of the data.

A Recommended Backup Strategy

The process of creating a database copy followed by exporting a BACPAC file (backup package) will give you a transactionally consistent (all of your data is in respect to all of the constraints on your database) backup file of the database. This advantage of this is that the backup is portable and you have the option to store it either in the storage on Windows Azure or on-premises locations.

You can use this file for a rollback plan to protect the database’s data against errors that can be generated by an application or due to user misuse, or in the unfortunate event of losing an entire region, in the case of Windows Azure.

Recommended Backup Tools

• Database copy
  

  The database copy feature will be responsible for creating a new database that will assure the transactional consistency principle is maintained in respect to the source database, at the time when the process of copying the database has finished. This means that during the copy process, any changes made to the source are also present on the copy. You have the possibility to create the copy on the same server as the source database or on another server.

  You can use the copy in the rollback plan if you want to make changes to either the schema or to the data, or in case there have been unwanted deletions or other modifications. You can also set up backups to be created automatically and this way setting the frequency and retention period for your database copies.

• SQL Database Import Export Service
  

  The SQL Database Import Export Service is responsible for copying the data and the object definitions from the source SQL Database which is stored in a Windows Azure environment to a BACPAC file. After this operation, there will be a bulk copy of the user data from tables to the BACPAC file. This file can then be stored either in the Blob storage service provided by Windows Azure, or it can be downloaded in order to act as protection against multiple region failures of the Azure service.

  To avoid being charged for each copy, you can set your database as read-only, and then you can export a BACPAC file straight from the database in order to create a copy that is transactionally consistent. However, by setting the database to a read-only state you lock the database, and access to it is not available until the export operation is complete and until the settings which correspond to its read-only state read-only are reverted.

Automation and Scheduling considerations for your backups

The operation of doing a backup of your Windows Azure SQL Database means doing a copy and then exporting a BACPAC file from the copy. In order to automate this process, you can make use of the settings in the SQL Database Export Service to set the frequency of your backups and also for the exported databases to set the retention period.

You can also create a program or a script that could do this operation automatically. The script or program should first copy the database, monitor the copy process and when the process is finished, proceed to do an export of the BACPAC file from the copy.

You also make a schedule for the program to run on a recurring mode by setting up a task scheduler similar to the Windows Scheduler or by using it with the Windows Azure worker role.