With the release of Service Update 4 for SQL Azure you now have the ability to make a snapshot of your running database on SQL Azure. This allows you to quickly create a backup before you implement changes to your production database, or to create a test database that resembles your production database.
The backup is performed in the SQL Azure datacenter using a transactional mechanism without downtime to the source database. The database is copied in full to a new database in the same datacenter. You can choose to copy to a different server (in the same data center) or the same server with a different database name.
A new database created from the copy process is transactionally consistent with the source database at the point in time when the copy completes. This means that the snapshot time is the end time of the copy, not the start time of the copy.
The Transact SQL looks like this:
CREATE DATABASE destination_database_name
AS COPY OF [source_server_name.]source_database_name
To copy the Adventure Works database to the same server, I execute this:
CREATE DATABASE [AdvetureWorksBackup] AS COPY OF [AdventureWorksLTAZ2008R2]
This command must be execute when connected to the master database of the destination SQL Azure server.
You can monitor the currently copying database by querying a new dynamic managed view called sys.dm_database_copies.
An example query looks like this:
Here is my output from the Adventures Works copy above:
When you copy a database to a different SQL Azure server, the exact same login/password executing the command must exist on the source server and destination server. The login must have db_owner permissions on the source server and dbmanager on the destination server. More about permissions can be found in the MSDN article: Copying Databases in SQL Azure.
One thing to note is that the server you copy your database to does not need to belong to the same service account. In fact you can give or transfer your database to a third party by using this database copy command. As long the user transferring the database has the correct permissions on the destination server and the login/password match you can transfer the database. I will show how to do this in a future blog post.
You will obtain the same resource allocation in the data center if you copy to the same server or a different server. Each server is just an endpoint – not a physical machine, see or blog post entitled: A Server Is Not a Machine for more details. So why copy to another server? There are two reasons:
More information about copying can be found in the MSDN article: Copying Databases in SQL Azure. Do you have questions, concerns, comments? Post them below and we will try to address them.
Awesome, a great first step in supporting database backups!
This is great, just in time for our first app release on azure :)
Why have previous comments (including mine) being removed from this post?
Niall: I think that your comments are on this blog post: blogs.msdn.com/.../10053883.aspx
So to copy your database as a backup... Is there a way to automate this?
Also, If I am paying $9.99/month for my database that takes 150 Megs, I imagine that I would have to pay another $9.99/month for this copy?
Do I understand correctly? Thanks.
WoodrowX: Currently there is no automation. Yes your understanding of the pricing is correct.
Hi Wayne, Sorry I was a bit to quick to point the finger. You are right my comments are on another post.
Does sp_executesql not support this new COPY command in SQL Azure?
Getting this error:
Incorrect syntax near 'COPY'.
When I run:
SELECT @SQL = 'CREATE DATABASE [' + @BackupDatabaseName + '] AS COPY OF [' + @DatabaseName + ']'
EXEC sp_executesql @SQL
Same statement with same names, but hard coded works fine.
Malex: Dynamic sql statement does not allow ‘Create Database’ statements in SQL Azure, the usual error is:
Msg 40530, Level 15, State 2, Line 1
The CREATE DATABASE statement must be the only statement in the batch.
I wouldn't expect the 'COPY OF' to work as long as the CREATE DATABASE doesn't. However, there is something very 'wrong' about the error message you are getting. I has passed your reproduction to the product team.
Does this new copy-only database incur an additional service and usage charge from Azure as another database?
I am planning to copy Production DB into UAT as backup Copy. However I am getting the following error :
CREATE DATABASE dbname_ProdBak AS COPY OF [servername.database.windows.net].dbnameinProd;
Msg 10054, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
As the UAT login credentials are not same in Prod, does it create the error?
How can I pass production credentials?
Thanks for in advance for quick help..
Permission are discussed here: msdn.microsoft.com/.../ff951624.aspx. I think this should answer your first question. Second answer is that you can't pass credentials.
I am still getting the following error : A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
Any idea how long this should run? I'm copying a 146 GB database to another SQL Azure server and it's only 70% done after 19 hours. It seems to be still running, as the percent_complete is ...slowly... increasing.