October05

Create new SQL Azure database as a copy

Did you know you can create a new SQL Azure database as a copy of an existing database?  Yes, you can.  There are some important limitations on how this works which I will cover below.  

Create the new database

-- create a new database as a copy
create database new_database as copy of original_database

In order to do this you must be logged in as the DBO of the original database.  See the NOTE in the article as well.  I think this is important difference for most database apps, the logins that are contained in the database must be updated.

Note After the cross-server copy process is complete, use the DBO login and the ALTER USER statement to map users in the new database to logins on the new SQL Azure server. For example: ALTER USER userName WITH LOGIN='loginName'. For more information, see ALTER USER (SQL Azure Database).

How do you know when it is done?

Now that’s great you started the copy, but how do you know when it is done?  There is TSQL syntax for that!

You can query the state from the sys.databases table to get the text like this:

select name, state, state_desc from sys.databases where name = 'new_database'

You will get back a response that should look like this:

name state state_desc
new_database 7 COPYING

 

Copy Progress

According to this MSDN Article you can also get the state while the COPYING is happening by querying sys.dm_database_copies. But there is a catch. Once the copy completes the rows are automatically removed, so be prepared to get nothing back. Even if you just got the COPYING above, there seems to be a slight lag where the dm_database_copies rows are removed before the sys.database entries are updated.

select * from sys.dm_database_copies where database_id = DB_ID('new_database')

That actually doesn’t work because the DB_ID will return NULL (the database doesn’t exist yet).

But just running the select without the where gives you something like this:

database_id start_date modify_date percent_complete error_code error_desc error_severity error_state
6 10/5/2011 4:30:12 PM +00:00 10/5/2011 4:30:12 PM +00:00 null null null null null

 

I have never seen the percent_complete change, or any of the error states be filled out (even when there was an error).  So I don’t know if this is intended to change in the future to be completed, or was removed.

The dm_database_copies MSDN page says that the information is only available on the master database context.

MSDN References

Copying Databases in Sql Azure

sys.dm_database_copies in SQL Azure

Comments are closed