Database Copy Command & Handling Asynchronous Execution in T-SQL

Database Copy T-SQL command in SQL Azure, provide the ability to create a identical copy of a database through a pure server side process. You can think of database copy as a backup followed by a restore of a database rolled into a single command. General documentation provides the details and use cases... Database Copy command works on any size database. Naturally this mean database copy on a large database is a long running operation. Much like the SQL Server Backup command, the operation takes a while to create a point in time consistent copy, synchronized to the completion of the command. One unique aspect of this command in SQL Azure is that, the system provides further robustness through built-in retry logic on the operation. For example, if a failover happens on a database that is being copied, the system retries the operation after failover without reporting a failure. Another unique aspect od database copy is that the operation is executed asynchronously. A long synchronous transaction would require a live connection and correct query timeout values to be in place for the whole duration of the database copy operation. When operating remotely on your database server over an internet connection, these requirements could be challenging. Database copy is kicked off through the following command;

 CREATE DATABASE destination_srv.destination_db 
AS COPY OF source_srv.source_db 

This creates the destination_db in a COPYING state on the destination server. This part happens synchronously. If there are errors during the creation of the database such as ‘database size quota is full’, these errors are returned immediately. The rest of the transfer after the creation of the destination database, is performed asynchronously.

Asynchronous nature of the command works great in most cases. However when scripting small database copy operations, is may get more challenging to write a script that creates a database through a copy command and then, just like the classic scripts today, wants to work with the new database in the next line of the script. Take the following script for example;

 -- connect to master and create the db as a copy of another db
CREATE DATABASE test100_copy AS COPY OF test100
GO 
-- connect to the test100_copy
-- and install schema * this step may fail since 
-- DB copy may not be done when the control returns to the script.
CREATE TABLE t1(c1 primary key, ...) 
GO

Good news is, it is fairly easy to convert an async operation to a synchronous operation through a simple script such as the one below; The additional steps block until the database copy is complete and destination database reach the ONLINE status.

 DROP DATABASE test100_copy
GO
-- connect to master and create the db as a copy of another db
CREATE DATABASE test100_copy AS COPY OF test100 
GO 
-- wait for the copy to finish DECLARE @start datetimeoffset
SELECT @start=getdate()
DECLARE @state_desc nvarchar(120)
DECLARE @dbid int
DECLARE @error_code int
DECLARE @error_desc nvarchar(4000)
DECLARE @error_severity int
DECLARE @error_state int
SELECT @dbid=database_id, @state_desc=state_desc FROM sys.databases 
WHERE name='test100_copy' 
WHILE (@state_desc!='ONLINE') 
BEGIN 
    IF (@state_desc!='COPYING') 
    BEGIN 
        SELECT TOP 1 @error_code=error_code, 
                @error_desc=error_desc, 
                @error_severity=error_severity, 
                @error_state=error_state 
        FROM sys.dm_database_copies WHERE database_id=@dbid 
        select @error_code, 
                @error_desc, 
                @error_severity, 
                @error_state
        RAISERROR (@error_code,@error_severity,@error_state);
        BREAK
    END 
    ELSE 
        -- delay for another 10 seconds 
        WAITFOR DELAY '00:00:10.000'
    SELECT @dbid=database_id, @state_desc=state_desc FROM sys.databases 
    WHERE name='test100_copy' 
END
SELECT 'Completed (in mins):',DATEDIFF(mi,@start,getdate())
GO
-- connect to the test100_copy and 
-- install schema * this step will not fail since DB copy is done
CREATE TABLE t1(c1 primary key, ...) 
GO

Enjoy.

-Cihan Biyikoglu