Migration massive de bases de données entre instances SQL Server : la procédure complète v.2.0

Une procédure complète basée sur T-SQL pour migrer des instances SQL Server — y compris les logins, les bases de données et les relocalisations de fichiers — sans outils externes requis.

Journal des modifications

(05/04/2014) Ajout de la compression pour réduire la bande passante, l’espace et le temps de transfert
Réduction de la valeur stat pour les très grandes bases de données
Ajout du paramètre de type de sauvegarde : FULL, FULL_COPYONLY ou DIFFERENTIAL
Ajout des paramètres Maxtransfersize et Buffercount pour améliorer les performances de sauvegarde (attention : nécessite plus de mémoire)
Correction d’un bug pour afficher les messages d’erreur corrects de tout type

Introduction

Il existe de nombreuses façons de migrer des bases de données : sauvegarde-restauration, détacher-copier-attacher, outil de copie de base de données SQL, etc. Cette procédure utilise l’approche sauvegarde-restauration — plus flexible que les autres et nécessitant moins de bande passante et d’espace disque. Chaque DBA doit choisir la bonne stratégie pour chaque scénario, mais cette approche est l’une des plus adaptables pour les instances complexes avec de nombreuses grandes bases de données.

Les macro-étapes sont :

Migrer les logins de l’instance en conservant le SID et le hash du mot de passe d’origine
Sauvegarder les bases de données et les transférer vers le site de destination
Restaurer les bases de données en relocalisiant les fichiers de données vers le nouveau système de fichiers

Étape 1 — Migrer les logins avec SID et hash de mot de passe d’origine

Cette étape transfère les logins depuis l’instance source en préservant à la fois le hash du mot de passe original et le SID original. L’alignement du SID entre les logins d’instance et les utilisateurs de base de données est critique pour éviter les utilisateurs orphelins après la restauration.

Exécutez le script suivant sur l’instance source et sauvegardez la sortie complète en texte (ALT+T dans SSMS avant l’exécution), puis exécutez la sortie sur l’instance de destination :

USE [master]
GO

CREATE FUNCTION [dbo].[fn_hexadecimal]
(
    @binvalue varbinary(256)
)
RETURNS VARCHAR(256)
AS
BEGIN
    DECLARE @charvalue varchar(256)
    DECLARE @i int
    DECLARE @length int
    DECLARE @hexstring char(16)
    SELECT @charvalue = '0x'
    SELECT @i = 1
    SELECT @length = DATALENGTH(@binvalue)
    SELECT @hexstring = '0123456789ABCDEF'
    WHILE (@i <= @length)
    BEGIN
        DECLARE @tempint int
        DECLARE @firstint int
        DECLARE @secondint int
        SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
        SELECT @firstint = FLOOR(@tempint/16)
        SELECT @secondint = @tempint - (@firstint*16)
        SELECT @charvalue = @charvalue +
            SUBSTRING(@hexstring, @firstint+1, 1) +
            SUBSTRING(@hexstring, @secondint+1, 1)
        SELECT @i = @i + 1
    END
    RETURN @charvalue
END
GO

-- Générer les scripts CREATE LOGIN pour tous les logins SQL
DECLARE @name      sysname
DECLARE @type      varchar(1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary(256)
DECLARE @PWD_string     varchar(514)
DECLARE @SID_varbinary  varbinary(85)
DECLARE @SID_string     varchar(514)
DECLARE @tmpstr        varchar(1024)
DECLARE @is_policy_checked varchar(3)
DECLARE @is_expiration_checked varchar(3)

DECLARE login_curs CURSOR FOR
    SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name,
           l.hasaccess, l.denylogin
    FROM sys.server_principals p
    LEFT JOIN sys.syslogins l ON l.name = p.name
    WHERE p.type IN ('S', 'G', 'U')
      AND p.name <> 'sa'
      AND p.name NOT LIKE '##%'

OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type,
    @is_disabled, @tmpstr, @hasaccess, @denylogin

WHILE (@@fetch_status = 0)
BEGIN
    IF (@type IN ('G','U'))
    BEGIN
        PRINT 'CREATE LOGIN [' + @name + '] FROM WINDOWS'
    END
    ELSE BEGIN
        SET @PWD_varbinary = CAST(LOGINPROPERTY(@name, 'PasswordHash') AS varbinary(256))
        EXEC [dbo].[fn_hexadecimal] @PWD_varbinary, @PWD_string OUT
        EXEC [dbo].[fn_hexadecimal] @SID_varbinary, @SID_string OUT
        SELECT @is_policy_checked     = CASE is_policy_checked WHEN 1 THEN 'ON' ELSE 'OFF' END
        FROM sys.sql_logins WHERE name = @name
        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' ELSE 'OFF' END
        FROM sys.sql_logins WHERE name = @name
        PRINT 'CREATE LOGIN [' + @name + '] WITH PASSWORD = ' + @PWD_string +
              ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @tmpstr + ']' +
              ', CHECK_POLICY = ' + @is_policy_checked +
              ', CHECK_EXPIRATION = ' + @is_expiration_checked
    END
    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type,
        @is_disabled, @tmpstr, @hasaccess, @denylogin
END

CLOSE login_curs
DEALLOCATE login_curs

Étape 2 — Sauvegarder et transférer les bases de données

Effectuez une sauvegarde complète de chaque base de données à migrer et transférez les fichiers vers le serveur de destination. L’utilisation de la compression (WITH COMPRESSION) réduit significativement la taille des fichiers et le temps de transfert réseau.

-- Sauvegarder toutes les bases de données utilisateur
DECLARE @dbname NVARCHAR(128)
DECLARE @path   NVARCHAR(512) = 'D:Backup'

DECLARE db_cursor CURSOR FOR
    SELECT name FROM sys.databases
    WHERE database_id > 4  -- Exclure les bases système
      AND state_desc = 'ONLINE'

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @cmd NVARCHAR(1000)
    SET @cmd = 'BACKUP DATABASE [' + @dbname + ']
        TO DISK = ''' + @path + @dbname + '.bak''
        WITH COMPRESSION, STATS = 5, CHECKSUM'
    EXEC sp_executesql @cmd

    FETCH NEXT FROM db_cursor INTO @dbname
END

CLOSE db_cursor
DEALLOCATE db_cursor

Étape 3 — Restaurer avec relocalisation des fichiers

-- Obtenir la liste des fichiers logiques dans la sauvegarde
RESTORE FILELISTONLY FROM DISK = 'D:BackupVotreBase.bak'

-- Restaurer avec relocalisation vers le nouveau chemin
RESTORE DATABASE [VotreBase]
FROM DISK = 'D:BackupVotreBase.bak'
WITH
    MOVE 'NomFichierLogique_Data' TO 'E:SQLDataVotreBase.mdf',
    MOVE 'NomFichierLogique_Log'  TO 'F:SQLLogVotreBase_log.ldf',
    STATS = 5,
    RECOVERY;