LLI Wiki — My Digital Knowledge Base

Copy an Azure SQL Database with wait and retry

T-SQL script that starts CREATE DATABASE AS COPY OF and tracks the copy through sys.dm_operation_status with timeout and retries.

In Azure SQL Database, CREATE DATABASE ... AS COPY OF starts an asynchronous copy. This T-SQL script runs a copy to a new, timestamp-named database, waits for it to finish and reports progress with retries and a timeout. Run it in the master database of the target logical server.

How it works

  1. Drop the target database if it already exists.
  2. Start the copy with CREATE DATABASE ... AS COPY OF.
  3. Poll sys.dm_operation_status until the copy is no longer in progress (state = 1), with a 10-second pause between checks, 3 retries (90 s apart) on error and an overall timeout.
  4. Poll again until the TERMINATE CONTINUOUS DATABASE COPY operation appears after the CREATE DATABASE COPY operation, which marks the end of the copy.

Script

-- ******************************************************
-- Local Declarations
-- ******************************************************
declare @msg varchar(255);
declare @ssql varchar(max);
declare @SourceServerName varchar(255) = 'sqlsrv-prd-app-01';
declare @SourceDBName varchar(255) = 'sqldb-prd-app-db01';
declare @TargetDBName varchar(255) = 'sqldb-prd-app-db01_202408251546';

-- Timeout settings (in seconds)
declare @timeoutSeconds int = 7200;  -- Timeout after 2 hours
declare @startTime datetime = getdate();

-- ******************************************************
-- Display the initial values
-- ******************************************************
select @SourceServerName as SourceServerName,
       @SourceDBName as SourceDBName,
       @TargetDBName as TargetDBName;

-- ******************************************************
-- Start the process and drop the existing database if it exists
-- ******************************************************
set @msg = convert(varchar(30), getdate(), 109) + ' - Copy started';
RAISERROR (@msg, 0, 1) WITH NOWAIT;

set @ssql = 'DROP DATABASE IF EXISTS [' + @TargetDBName + ']';
exec(@ssql);

-- ******************************************************
-- Start the database copy process
-- ******************************************************
set @ssql = 'CREATE DATABASE [' + @TargetDBName + '] AS COPY OF [' + @SourceServerName + '].[' + @SourceDBName + ']';
exec(@ssql);

-- Check for errors
IF @@ERROR <> 0
BEGIN
    RAISERROR ('Error occurred during database creation.', 16, 1);
    RETURN;
END

-- Indicate partial completion of the copy process
set @msg = convert(varchar(30), getdate(), 109) + ' - Copy partially completed';
RAISERROR (@msg, 0, 1) WITH NOWAIT;

-- ******************************************************
-- Wait for the asynchronous copy to complete with timeout
-- ******************************************************
declare @retryAttempts int = 0;
declare @maxRetryAttempts int = 3;
WHILE 1 = 1
BEGIN
    BEGIN TRY
        -- Check the state of the asynchronous copy
        IF NOT EXISTS (SELECT 1 FROM sys.dm_operation_status
                       WHERE major_resource_id = @TargetDBName
                       AND state = 1)
        BEGIN
            -- Copy finished: leave the loop
            BREAK;
        END
    END TRY
    BEGIN CATCH
        -- Error handling
        IF @retryAttempts < @maxRetryAttempts
        BEGIN
            set @retryAttempts = @retryAttempts + 1;
            set @msg = convert(varchar(30), getdate(), 109) + ' - Error occurred while checking copy status. Retrying in 90 seconds (' + CAST(@retryAttempts AS VARCHAR) + '/' + CAST(@maxRetryAttempts AS VARCHAR) + ')';
            RAISERROR (@msg, 0, 1) WITH NOWAIT;

            -- Wait 90 seconds before retrying
            WAITFOR DELAY '00:01:30';
            CONTINUE;
        END
        ELSE
        BEGIN
            RAISERROR ('Maximum retry attempts exceeded while checking copy status. Exiting.', 16, 1);
            RETURN;
        END
    END CATCH

    -- Timeout check
    IF DATEDIFF(SECOND, @startTime, GETDATE()) > @timeoutSeconds
    BEGIN
        RAISERROR ('Timeout exceeded while waiting for copy to complete', 16, 1);
        RETURN;
    END

    set @msg = convert(varchar(30), getdate(), 109) + ' - Waiting for copy to complete(1)';
    RAISERROR (@msg, 0, 1) WITH NOWAIT;

    -- Reset the retry counter when the query succeeded
    set @retryAttempts = 0;

    WAITFOR DELAY '00:00:10';
END

-- Reset start time for the next loop
set @startTime = getdate();

-- ******************************************************
-- Verify the continuous copy termination with timeout
-- ******************************************************
set @retryAttempts = 0;
WHILE 1 = 1
BEGIN
    BEGIN TRY
        -- Check that the continuous copy was terminated
        IF EXISTS (SELECT 1 FROM sys.dm_operation_status
                   WHERE major_resource_id = @TargetDBName
                   AND operation = 'TERMINATE CONTINUOUS DATABASE COPY'
                   AND last_modify_time > (SELECT max(last_modify_time)
                                           FROM sys.dm_operation_status
                                           WHERE major_resource_id = @TargetDBName
                                           AND operation = 'CREATE DATABASE COPY'))
        BEGIN
            BREAK;
        END
    END TRY
    BEGIN CATCH
        IF @retryAttempts < @maxRetryAttempts
        BEGIN
            set @retryAttempts = @retryAttempts + 1;
            set @msg = convert(varchar(30), getdate(), 109) + ' - Error occurred while checking termination status. Retrying in 90 seconds (' + CAST(@retryAttempts AS VARCHAR) + '/' + CAST(@maxRetryAttempts AS VARCHAR) + ')';
            RAISERROR (@msg, 0, 1) WITH NOWAIT;

            WAITFOR DELAY '00:01:30';
            CONTINUE;
        END
        ELSE
        BEGIN
            RAISERROR ('Maximum retry attempts exceeded while checking termination status. Exiting.', 16, 1);
            RETURN;
        END
    END CATCH

    IF DATEDIFF(SECOND, @startTime, GETDATE()) > @timeoutSeconds
    BEGIN
        RAISERROR ('Timeout exceeded while waiting for termination of continuous copy', 16, 1);
        RETURN;
    END

    set @msg = convert(varchar(30), getdate(), 109) + ' - Waiting for termination of continuous copy';
    RAISERROR (@msg, 0, 1) WITH NOWAIT;

    set @retryAttempts = 0;

    WAITFOR DELAY '00:00:10';
END

-- Final message indicating the completion of the process
set @msg = convert(varchar(30), getdate(), 109) + ' - Copy completed';
RAISERROR (@msg, 0, 1) WITH NOWAIT;

GO

Notes

Mastodon LLIMastodon SocialFosstodon