Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Creating backups and copies of your SQL Azure databases


By:   |   Last Updated: 2011-01-25   |   Comments (2)   |   Related Tips: More > 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


Last Updated: 2011-01-25


next webcast button


next tip button



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

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, October 23, 2012 - 12:35:33 PM - Dx Back To Top

Just great, thank you!


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

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

 


Learn more about SQL Server tools