Restauration de sauvegarde automatique entre instances

Les scripts suivants permettent de restaurer automatiquement des sauvegardes de bases de données entre serveurs, sans recopier les fichiers de sauvegardes d’un serveur à l’autre : ils sont particulièrement utiles en cas de migration ou de synchronisation entre instances. En outre, ils permettent des restaurations incrémentielles successives basées sur les sauvegardes de journal.

Le principe est le suivant :

  • Sur l’instance source, on crée une nouvelle vue dans msdb pour récupérer l’historique et la séquence de sauvegardes effectuées
  • Sur le serveur source, on partage le répertoire qui contient les sauvegardes et on donne les permissions de lecture au compte de service de l’instance de destination.
  • Sur l’instance de destination, on crée un serveur lié qui pointe vers l’instance source
  • Sur l’instance de destination, on crée une base de données de travail qui contient les fonctions et procédures stockées de gestion, ainsi qu’une table qui liste les bases à restaurer. On renseigne dans la table la liste des bases à restaurer, on peut aussi préciser un nouveau nom de base pour la restauration.
  • Sur l’instance de destination, on exécute la procédure stockée. Elle effectue automatiquement la restauration des bases que l’on a configuré : elle va directement chercher l’historique (à travers le serveur lié) et lire les fichiers de sauvegardes sur le serveur source (à travers le partage). Entre autres paramètres, un des paramètres de la procédure vous permet de configurer s’il s’agit d’une initialisation ou d’une synchronisation incrémentale par restauration successive de nouvelles sauvegardes de journal.

Vue à créer dans la base msdb de l’instance source :

Puis, n’oubliez pas de créer le serveur lié et le partage, ainsi que configurer les permissions nécessaires.

Base de données _SQL_MIGRATION_ et ses objets,  à créer sur l’instance de destination

La procédure stockée de restauration, à créer sur la base _SQL_MIGRATION_ précédemment créée

Les paramètres sont les suivants :

@SourceLinkedServer NVARCHAR(64) — Nom du serveur lié source des sauvegarde (sous la forme SERVEUR\INSTANCE)
@DatabaseToRestoreTable NVARCHAR(256) — Nom de la table qui contient la liste des bases à restaurer
@PathBackupRetrieval NVARCHAR(256), — chemin où aller chercher les sauvegardes (le nom du partage)
@PathBackupOriginal NVARCHAR(256) — chemin où sont faites les sauvegardes sur la source (partie qui sera remplacée par @PathBackupRetrieval)
@PathDestinationData NVARCHAR(256) — chemin du répertoire de destination des fichiers de données de la base à restaurer
@PathDestinationLog NVARCHAR(256) — chemin du répertoire de destination des fichiers de journal à restaurer
@Init BIT — à 1, initialisation, à 0 restore les nouvelles sauvegardes non encore restaurées
@Debug BIT — à 1 (par défaut) n’exécute pas et renvoie le script, à 0 il execute. En debug, ne met pas à jour le statut.
@OnlyFullBackups BIT = 0 — à 1, ne considère que les backups FULL
@NoRestoreLogs BIT = 0 — à 1, ne traite pas les sauvegardes de journal (mais les différentielles sont traitées)
@SourceSQLVersion INT — paramètre non utilisé actuellement
@SetOfflineBefore BIT = 0 — à 1, si la base existe, la met OFFLINE WITH ROLLBACK IMMEDIATE avant restauration
@DoRecovery BIT = 0 — à 1, effectue le RECOVERY de chaque base après la dernière restauration de la séquence
@TargetCompatibility INT — si renseigné et @DoRecovery activé, niveau de compatibilité à configurer après restauration

Exemple d’utilisation :

EXEC dbo.AutoRestoreDB @SourceLinkedServer = N’MONK\SQL2016′ , @DatabaseToRestoreTable = N’T_DatabasesToRestore’ , @PathBackupRetrieval = N’\\server\sharebackup\’ , @PathBackupOriginal = N’c:\temp\’ , @PathDestinationData = N’C:\MSSQLData\MSSQL14.SQL2017\MSSQL\Data’ , @PathDestinationLog = N’C:\MSSQLData\MSSQL14.SQL2017\MSSQL\Data’ , @Init = 1 , @Debug = 1 , @OnlyFullBackups = 0 , @NoRestoreLogs = 0 , @SourceSQLVersion = NULL , @SetOfflineBefore = 1 , @DoRecovery = 1 , @TargetCompatibility = 140