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