LLI Wiki — My Digital Knowledge Base

Copier une base Azure SQL Database avec attente et nouvelles tentatives

Script T-SQL qui lance CREATE DATABASE AS COPY OF et suit la copie via sys.dm_operation_status avec délai maximal et reprises.

Dans Azure SQL Database, CREATE DATABASE ... AS COPY OF lance une copie asynchrone. Ce script T-SQL copie une base vers une nouvelle base nommée avec un horodatage, attend la fin de la copie et affiche l'avancement, avec nouvelles tentatives et délai maximal. Il s'exécute dans la base master du serveur logique cible.

Fonctionnement

  1. Supprimer la base cible si elle existe déjà.
  2. Lancer la copie avec CREATE DATABASE ... AS COPY OF.
  3. Interroger sys.dm_operation_status jusqu'à ce que la copie ne soit plus en cours (state = 1), avec une pause de 10 secondes entre les vérifications, 3 nouvelles tentatives (espacées de 90 s) en cas d'erreur et un délai maximal global.
  4. Interroger à nouveau jusqu'à ce que l'opération TERMINATE CONTINUOUS DATABASE COPY apparaisse après l'opération CREATE DATABASE COPY, ce qui marque la fin de la copie.

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

Remarques

Mastodon LLIMastodon SocialFosstodon