Share via

Different ways to Backup your Windows Azure SQL Database

  • Article

 

Windows Azure SQL database Backup and Restore Strategy:

Overall Introduction:

Backing up and restoring data is different in Windows Azure SQL Database than an on-premise SQL Server and must work with the available resources and tools. This topic introduces the concepts and the functionality available currently to back up and restore the data within Windows Azure SQL Database.

Database backups protect the database against data loss caused by hardware failures, user, or application errors or a widespread loss of regional facilities. Windows Azure has built-in fault tolerance to protect your data from individual server, network, and device failures.

Please be aware that Windows Azure SQL databases are replicated to different servers / datacenters for disaster recovery but are not "Backed up" for the purpose of recovering accidental data loss or changes.  

In order to protect your data against user/application errors or a total loss of a region you must create your own backup of the data. The following are the options that are available to you to create a backup of the data and schema:

Reference:

Windows Azure SQL Database Backup and Restore Strategy -- https://social.technet.microsoft.com/wiki/contents/articles/1792.windows-azure-sql-database-backup-and-restore-strategy.asp

Business Continuity in Windows Azure SQL Database (Architecture and Design to protect from widespread loss of data) -- https://msdn.microsoft.com/en-us/library/hh852669.aspx

Here's the Compiled List of Windows Azure SQL Database Backup options

1. SQL Azure Migration Wizard

Introduction:

SQL Database Migration Wizard (SQLAzureMW) is an open source application that has been used by thousands of people to migrate their SQL database to and from Windows Azure SQL Database. SQLAzureMW is a user interactive wizard that walks a person through the analysis / migration process. One of the main requests from the SQLAzureMW community was to take the user interactive wizard and make it command line driven interface so that it could be used in an automated backup process to back up their Windows Azure SQL Database schema / data to a data store for disaster recovery. I am excited to let you know that SQLAzureMW now has two tools that can be used from a command line.

Supportability:

SQL Database Migration Wizard is an open-source application, as a result, Microsoft doesn't support it officially.

Reference:

SQL Database Migration Wizard Installation Guide https://sqlazuremw.codeplex.com/

2. Windows Azure SQL Database: Create a database Copy

Introduction:

T-SQL: CREATE DATABASE destination_database_name AS COPY OF [source_server_name].source_database_name

This feature allows you to copy a running database creating another fully functional SQL Azure database in the same data center

  • Database copies can be created on the same or different Windows Azure SQL Database server, but the server must be in the same region
  • Database copies are charged at the same rate as the source database when the copy is complete and are counted towards the 150 databases per server limit.
  • Must complete within 24 hours
  • You can monitor the copy process by using the sys.dm_database_copies and sys.databases views on the destination server

Supportability:

This is one of Azure SQL Database features, as a result, Microsoft support it officially.

Reference:

General Steps to create database copy https://msdn.microsoft.com/en-us/library/jj650016.aspx#copy

Detailed Steps to create database copy https://msdn.microsoft.com/en-us/library/ff951631.aspx

3. Windows Azure SQL Database Import/Export Service

Introduction:

Directly import or export between a Windows Azure SQL database and Windows Azure BLOB storage. This feature is a free service exposed through the Azure Management Portal and exports all supported database schema objects and table data in a single file package with an extension of .BACPAC. One point to note is that the .BACPAC file is not equivalent to a Backup as it does not contain Transaction Log and History Data and is not transactionally consistent by itself.

Supportability: Microsoft Support it officially.

Reference:

General Steps to create a backup (BACPAC) using SQL Database Import/Export Service https://msdn.microsoft.com/en-us/library/jj650016.aspx#import_export

Detailed Steps to create a backup (BACPAC) using SQL Database Import/Export Service https://msdn.microsoft.com/en-us/library/f6899710-634e-425a-969d-8db1267e9471#Export

4. Windows Azure SQL Database: Data-Tier Application Framework

Introduction:

The SQL Server Data-tier Application (DAC) framework is a component based on the .NET Framework that provides application lifecycle services for database development and management. Application lifecycle services include extract, build, deploy, upgrade, import, and export for data-tier applications in SQL Database, SQL Server code named 'Denali' CTP 3, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005, and limited support for SQL Server 2000.

Supportability: This is a new feature of SQL Azure released in 2011 Q4, as a result, Microsoft Support it officially.

Reference:

General Steps to create a backup (BACPAC) using Data-tier Application https://msdn.microsoft.com/en-us/library/jj650016.aspx#SSMS

Detailed Steps to create a backup (BACPAC) using Data-tier Application https://msdn.microsoft.com/en-us/library/hh213241

Data-tier Application Installation Guide: https://social.technet.microsoft.com/wiki/contents/articles/2639.aspx

Data-tier Application Framework Whitepaper:https://msdn.microsoft.com/en-us/library/ff381683(SQL.100).aspx

5. Windows Azure SQL Database: SQL Data Sync

Introduction:

Microsoft SQL Data Sync is built upon the Microsoft Sync Framework. SQL Data Sync enables you to easily create and schedule bi-directional synchronizations from within the SQL Data Sync web site without the need to write a single line of code. SQL Data Sync supports synchronizations between multiple Windows Azure SQL databases and between SQL Server and SQL databases so you can create custom synchronization groups that fit your business requirements.

Supportability:

This features is still in Customer Preview Period, it is only supported by Forum now. Microsoft will support it officially in the future.

Reference:

SQL Data Sync General Guide https://msdn.microsoft.com/en-us/library/hh456371.aspx

Detailed Steps to create a Sync Group https://msdn.microsoft.com/en-us/library/hh667311.aspx

6. SSIS (SQL Server Integration Service) and BCP (Bulk Copy):

These tools can be used to back up data from SQL Azure to on-premise servers. https://blogs.msdn.com/b/sqlazure/archive/2010/05/21/10014019.aspx

Also you can use these third party tools https://bcp2sqlazure.codeplex.com/ and https://sqlazurebackup.codeplex.com/

7. Windows Azure SQL Database to Windows Azure BLOB Storage: Third Party Tools: such as (Red-gate)

Introduction: A third party tool to schedule backup from SQL Azure to Windows Azure BLOB Storage.

Supportability:This is a third party tool, as a result, Microsoft doesn't support it officially. But you can ask Redgate for support.

Reference:Microsoft Partner: Redgate Windows Azure SQL Database Backup https://www.red-gate.com/products/dba/sql-azure-backup/

8. Windows Azure SQL Database: Point-In-Time Restore (PITR)

Introduction:

The Windows Azure SQL Database Point in Time Restore feature is designed to recover user data from user or application errors. It is a UI based feature that allows users to restore their database back to an arbitrary point in time within the past 2 weeks.

Supportability:

This feature is still in Private Preview, and has not been released. Microsoft doesn't support it currently.

Reference: Microsoft SQL Azure Point in Time Restore Preview Privacy Statement https://www.microsoft.com/en-us/download/confirmation.aspx?id=28364

Comments

  • Anonymous
    April 18, 2013
    For automating Azure SQL backups you can use SQLBackupAndFTP - the backup could be compressed, encrypted and sent a folder, FTP, Dropbox, Amazon S3, SkyDrive, etc.

  • Anonymous
    May 18, 2013
    Perform quick backup and recovery with how to repair sql 2005 database .mdf files Article www.repairmdf.sqlserverrepairtoolbox.com will open all secrets about backup&restore sql databases

  • Anonymous
    June 11, 2014
    how can I upload my local database to my remote database in a VM in Azure ? both sql server express 2014 ? is there any web or info ?

  • Anonymous
    December 26, 2014
    Great stuff on backups with Azure, I also stumbled upon this great article which is a beginner's guide to SQL Azure sqlturbo.com/beginners-guide-to-microsoft-azure-sql-database