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
- Drop the target database if it already exists.
- Start the copy with
CREATE DATABASE ... AS COPY OF. - Poll
sys.dm_operation_statusuntil 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. - Poll again until the
TERMINATE CONTINUOUS DATABASE COPYoperation appears after theCREATE DATABASE COPYoperation, 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
- Server and database names are generic examples; the target name carries a
yyyyMMddHHmmsuffix to make it unique. - The timeout is set to 7200 seconds (2 hours) and applies separately to each waiting loop.
- The
DROP DATABASE IF EXISTSstep removes any previous target database: check the target name before running.