Windows Azure SQL Database Backup and Restore

Creating a copy:

The database copy feature creates a new database, which is a copy of the source SQL Database. The new database 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, all changes to the source are replicated to the copy. The copy can be used in the rollback plan for changes to schema or data, and unwanted deletion or modification. Creating a copy provides protection against user or application errors.

Using T-SQL statements you can create a copy of the source database, on the same or different Windows Azure SQL Database server. From a database backup perspective, copying the database on a recurring schedule is like taking full backups of an on-premise SQL Server database. By copying a database on a recurring schedule, you can create copies on a daily schedule or a schedule that fits your data recovery plan.

Following is a simple T-SQL example to create a copy of the source database:

CREATE DATABASE destination_database_name 
AS COPY OF [source_server_name].source_database_name

The following are some important considerations when you use database copy:

• 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.
  
  

For detailed step by step instructions for copying a SQL Database, see How to: Copy Your Databases (Windows Azure SQL Database).

For other considerations and details, see How to help protect your database from unwanted deletions or modifications.

Restoring the data and schema:

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. The following code example illustrates renaming the source and copy on the same server:

ALTER DATABASE Database1 
MODIFY NAME = Database1_OLD
GO
WAITFOR DELAY '00:00:30'
GO
ALTER DATABASE Database1_copy_02_01_2012
MODIFY NAME = Database1
GO

The WAITFOR DELAY is included in the statement to make sure that new connections formed during this process do not accidentally connect to the old database before the renaming is completed. For more details, see the Database Copy Restore section in How to help protect your database from unwanted deletions or modifications.

Export data and schema to a BACPAC file:

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 then followed by a bulk copy of the data from the user tables to the BACPAC file. To make sure that you have an isolated copy of the data which is transactionally consistent, you must first create a database copy, and then create the BACPAC file from the copy. Alternatively, you can also prevent modifications to the data in the databases during export by limiting access to the databases through permissions or connections. Use the following steps to create a backup of the SQL Database.

To export a BACPAC file

For detailed step by step instructions on how to export a database, see Export a Database.

Restoring the Windows Azure SQL Database data and schema:

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.

For step by step instructions on how to import a database, see Import a Database.

This is similar to the process described in the SQL Database Import/Export service section, but the BACPAC file is created by using SSMS.

Create a backup:

To export a BACPAC file

For step by step instructions, see Export a Data-tier Application

Restoring the Windows Azure SQL Database data and schema:

Using the Import Data-tier Application task, 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. For step by step instructions, see Import a BACPAC file to Create a New Database.