Azure database copy, wait for ready state

Question

I am making new Azure databases now and then and without any human interaction, by using a template database and azure copy database t-sql script.

After reading a few caveats I wonder what is the best way to wait for the copied database to be ready.

I have posted my own answer but it could end up in an infinite loop if the database copy fails.

What is the best way to be sure that the copying is done. This is very important as you are only allowed one copy database command at a time. I'm using an Azure worker with a c# queue that creates the databases as nessesary.

Answer 1

This is how I'm doing it now. This should work but unfortunatly errors when copying are rare.

    declare 
        @sName varchar(max),
        @sState varchar(max),
        @iState int,
        @sDbName varchar(50)

    set @sDbName = 'myDbName'
    set @iState = 7 --copying
    while @iState = 7
    begin
        WAITFOR DELAY '00:00:05'
        SELECT @sName = name, @iState = state, @sState = state_desc FROM sys.databases WHERE name = @sDbName
    end
    SELECT name, state,state_desc FROM sys.databases WHERE name = @sDbName

This code is actually run from a Azure worker in c#

Edit:

If you want more granular approch use sys.dm_database_copies

But otherwise remember that this sql returns the state as integer, and its only successfull if the state ==0 Othervise the copy has failed. the states are:

0 = ONLINE
1 = RESTORING
2 = RECOVERING
3 = RECOVERY_PENDING
4 = SUSPECT
5 = EMERGENCY
6 = OFFLINE
7 = COPYING (Applies to Windows Azure SQL Database)
Windows Azure SQL Database returns the states 0, 1, 4, and 7.