By: Arshad Ali | 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.
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.
SELECT * FROM sys.dm_database_copies
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.
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.
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
