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
- Supprimer la base cible si elle existe déjà.
- Lancer la copie avec
CREATE DATABASE ... AS COPY OF. - Interroger
sys.dm_operation_statusjusqu'à 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. - Interroger à nouveau jusqu'à ce que l'opération
TERMINATE CONTINUOUS DATABASE COPYapparaisse après l'opérationCREATE 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
- Les noms de serveur et de base sont des exemples génériques ; le nom cible porte un suffixe
yyyyMMddHHmmpour être unique. - Le délai maximal est de 7200 secondes (2 heures) et s'applique séparément à chaque boucle d'attente.
- L'étape
DROP DATABASE IF EXISTSsupprime toute base cible précédente : vérifier le nom cible avant l'exécution.