Creating backups and copies of your SQL Azure databases

By: Arshad Ali | Updated: 2011-01-25 | Comments (3) | Related: > Azure

Problem

As a DBA you always followed a practice to back up your database (or take a snapshot of your database) before making any changes so that you can revert to your old database state if something goes wrong. Also to setup a development or test environment you use a backup of your database and restore it in the respective environment. If you are moving to SQL Azure, what would you do in these cases as backup / restore and database snapshots are not supported as of now?

Solution

SQL Azure provides a feature to make a copy of your database which you might need during your application upgrade or while setting up a development or test environment or as an alternative to backups. The database copy process is asynchronous, which means the database copy command returns immediately and you don't need an active connection while copying since the actual copy is done by SQL Azure in the background.

You can monitor the progress of the database copy using the provided DMVs/catalog views. Please note as long as the database copy operation is in progress the original/source database needs to be online as the copy operation is dependent on it (if you drop the source database the copy operation will be cancelled). The moment the copy operation is complete the newly copied database is independent of the source database and fully functional as any regular database.

Initiating the Copy Database Operation

To create a copy, use the regular CREATE DATABASE command and provide the "AS COPY OF" clause followed by the source database name. If your database is in the same SQL Azure server you can simply specify the database name otherwise you will need to specify the SQL Azure Server name too such as ServerName.DatabaseName.

The command below creates an exact copy of your source database with same edition and maximum database size.

--Script #1 - Creating a database copy
CREATE DATABASE AdventureWorksCopy
AS COPY OF AdventureWorks
GO

Monitoring the Copy Database Operation

To monitor the progress of the copy, you can query sys.databases catalog view to see the state of your database copy. If the copy operation is in progress its state would be COPYING as you can see below. In addition, SQL Azure provides a new DMV, sys.dm_database_copies, which tracks the progress and reports the percent complete for the copy as you can see below.

--Script #2 - Package wise events
SELECT * FROM sys.dm_database_copies
SELECT state_desc, * FROM sys.databases

sql azure provides a new dmv that tracks the progress and reports the percent complete for the copy

During the copy operation the database is visible in SSMS, but it is not available for use and no objects are visible as you can see in the image below.

during the copy operation the database is visible in ssms, but is not available for use

The moment the copy operation is complete, the new copied database will be in an ONLINE state and available for use. If for any reason the copy operation fails the database state will be SUSPECT, in that case you will need to drop it and retry the copy again.

once completed, the new copied database will be in an online state

if the copy operation fails the database state will be suspect

Notes

SQL Azure sets a limit of 150 databases on a single SQL Azure server and it applies to the newly copied/created databases too. In other words, the newly copied database is counted towards this limitation and also the billing applies as a regular database. For details about SQL Azure billing click here.
While creating a copy of the database, connect to master database on the destination server and run the CREATE DATABASE command as discussed above. You must use a login with the same user name and password at both places, it should be a database owner (dbo) on the source database and a member of the dbmanager role or server level principal login. If you are creating a copy across servers, the login that you use to copy the database becomes the database owner (dbo) on the new database when it is created and is assigned a new security identifier (SID).
The newly copied/created database will be transactionally consistent with the source database at the time when the copy operation completes.

Next Steps

Review How to: Back Up Your Database (SQL Azure) article on msdn.
Review How to: Copy Your Database to a Different Server (SQL Azure) article on msdn.
Review other SQL Azure related tips

About the author

Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2011-01-25

Comments For This Article

Thursday, December 22, 2022 - 7:24:31 AM - John Trollope	Back To Top (90774)
Does this no work on SQL Managed instance?? Thanks, John set nocount on select @@version go CREATE DATABASE dbatest AS COPY OF dba GO Microsoft SQL Azure (RTM) - 12.0.2000.8 Nov 16 2022 04:43:19 Copyright (C) 2022 Microsoft Corporation Msg 102, Level 15, State 1, Line 8 Incorrect syntax near 'copy'.

Tuesday, October 23, 2012 - 12:35:33 PM - Dx	Back To Top (20054)
Just great, thank you!

Thursday, February 10, 2011 - 3:38:24 AM - Gaurav Mantri

Hi Arshad,

May I add that we've a product called Azure Management Cmdlets (http://www.cerebrata.com/Products/AzureManagementCmdlets) which has a database backup powershell cmdlet. What it does is that it downloads table data using BCP utility on your desktop. This cmdlet can also push this backup data in Azure Blob Storage as well.

Thought I should mention it for your readers.

Regards

Gaurav Mantri

http://www.cerebrata.com