Comment restaurer plusieurs bases de données dans SQL Server (3 méthodes)

Est-ce que vous pouvez restaurer plusieurs bases de données SQL en une seule fois

Il est regrettable que l"interface graphique SSMS ne supporte que la restauration d"une seule base de données à la fois. Si vous avez sauvegardé plusieurs bases de données dans plusieurs fichiers bak, pouvez-vous les restaurer tous en une seule fois?

La réponse est OUI. En général, vous pouvez utiliser un script T-SQL valide pour restaurer plusieurs bases de données en une seule fois, mais seulement si les fichiers de sauvegarde sont nommés exactement comme les bases de données correspondantes et n"ont aucune information supplémentaire comme la date/heure.

Si vous souhaitez le faire de manière plus flexible, il existe également une alternative facile avec l"interface graphique. Choisissez simplement la méthode qui vous convient.

Méthode 1. Restaurer plusieurs bases de données à partir de fichiers bak en utilisant xp_cmdshell

Vous pouvez utiliser un script pour restaurer plusieurs bases de données dans SQL Server à partir d"un dossier. Mais avant cela, veuillez vous connecter à l"instance et activer xp_cmdshell.

1. Cliquez sur Nouvelle requête et saisissez la commande suivante:

-- Pour autoriser la modification des options avancées.
EXEC sp_configure "show advanced options", 1;
GO
-- Pour mettre à jour la valeur actuellement configurée pour les options avancées.
RECONFIGURER;
GO
-- Pour activer la fonctionnalité.
EXEC sp_configure "xp_cmdshell", 1;
GO
-- Pour mettre à jour la valeur actuellement configurée pour cette fonctionnalité.
RECONFIGURER;
GO

Exécutez la requête, et la fonctionnalité sera activée.

2. Ensuite, vous pouvez restaurer plusieurs bases de données de SQL Server en utilisant le script suivant (veuillez remplacer "D:\backup\" par le dossier qui contient tous les fichiers de sauvegarde correspondants):

DÉCLARER @FilesCmdshell TABLE (
outputCmd NVARCHAR (255)
)
DÉCLARER @FilesCmdshellCursor CURSOR
DÉCLARER @FilesCmdshellOutputCmd AS NVARCHAR(255)

INSÉRER DANS @FilesCmdshell (outputCmd) EXEC master.sys.xp_cmdshell "dir /B D:\backup\*.bak"
DÉFINIR @FilesCmdshellCursor CURSEUR POUR SÉLECTION outputCmd FROM @FilesCmdshell

OUVRIR @FilesCmdshellCursor
FETCH NEXT FROM @FilesCmdshellCursor INTO @FilesCmdshellOutputCmd
TANT QUE @@FETCH_STATUS = 0
DEBUT
DÉCLARER @cmd NVARCHAR(MAX) = "RESTORE DATABASE [" SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX(".", @FilesCmdshellOutputCmd)) "] FROM DISK = N"'D:\backup\" SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX(".", @FilesCmdshellOutputCmd)) ".bak"' WITH FILE = 1, NOUNLOAD, STATS = 10"
EXEC(@cmd)

FETCH NEXT FROM @FilesCmdshellCursor INTO @FilesCmdshellOutputCmd
FIN

✎Note: Si vous recevez une erreur «La fin du journal de la base de données n"a pas été sauvegardée. Utilisez BACKUP LOG WITH NORECOVERY pour sauvegarder le journal s"il contient des opérations que vous ne souhaitez pas perdre. Utilisez la clause WITH REPLACE ou WITH STOPAT dans l"instruction RESTORE pour simplement écraser le contenu du journal», la solution est simplement celle indiquée.

Par exemple, vous pouvez modifier la commande de restauration en "...WITH FILE = 1, REPLACE, NOUNLOAD, STATS = 10"

Voie 2. Générer un script pour restaurer toutes les bases de données dans SQL Server

Alternativement, vous pouvez utiliser un script pour générer des commandes de restauration pour toutes les bases de données dans SQL Server, et les combiner en un nouveau script à exécuter.

1. Cliquez sur Nouvelle requête et entrez les commandes suivantes dans la fenêtre SQLQuery:

DÉCLARE @folderpath VARCHAR (1000)
SÉLECTIONNEZ @folderpath = "D:\Backup\" -- Emplacement de sauvegarde
SÉLECTIONNEZ "RESTORER BASE DE DONNÉES[" NOM "] À PARTIR DU DISQUE = "'" @folderpath name ".bak"' AVEC NORECOVERY,
REMPLACER, STATS = 5"
DEPUIS master.sys.databases
WHERE name NOT IN ("master","model","msdb","tempdb","distribution")

Cela va générer une série de commandes pour restaurer plusieurs bases de données dans SQL Server à partir de fichiers .bak portant les mêmes noms.

2. Faites un clic droit sur n"importe quelle commande dans Résultats, choisissez Tout sélectionner puis Copier (ou utilisez Ctrl A et Ctrl C).

3. Collez ces commandes dans la fenêtre SQLQuery en tant que nouveau script à Exécuter. Cela restaurera toutes les bases de données de SQL Server à partir des fichiers .bak avec les noms de fichiers correspondants.

Manière 3. Sauvegarder et restaurer plusieurs bases de données SQL avec une interface graphique (la plus simple)

Les scripts ci-dessus s"appliquent uniquement lorsque vos fichiers .bak portent exactement les noms des bases de données SQL, ce qui est assez restrictif en pratique. Par conséquent, je souhaite fournir une méthode plus pratique pour sauvegarder et restaurer plusieurs bases de données, voire l"ensemble de l"instance.

AOMEI Cyber Backup est une solution de gestion centralisée fiable pour sauvegarder et restaurer les bases de données SQL sur tous les ordinateurs de bureau, portables et serveurs dans le réseau local.

Il fonctionne sur Windows 11/10/8.1/8/7, Windows Server 2022/2019/2016/2012 (R2)/2008 (R2) et prend en charge la sauvegarde de SQL Server 2005 à 2022. Outre les bases de données SQL, il prend également en charge la sauvegarde de machines virtuelles Hyper-V et VMware.

• Exécutez le fichier .exe téléchargé pour installer AOMEI Cyber Backup et lancez son interface principale. Cliquez sur Appareil source -> Microsoft SQL -> Ajouter Microsoft SQL.

• Choisissez Télécharger le programme proxy ou Copier le lien pour télécharger le programme Agent et l"installer sur l"appareil avec SQL Server installé. Ensuite, passez à Proxy déjà installé pour sélectionner l"appareil.
• Ensuite, cliquez sur -> Authentification pour valider l"instance de la base de données. Vous pouvez choisir Authentification Windows ou Authentification SQL.

C"est tout, vous pouvez maintenant planifier la sauvegarde des données pour le serveur SQL ajouté.

◉ Comment sauvegarder plusieurs bases de données dans SQL Server :

1. Cliquez sur Tâche de sauvegarde -> Créer une nouvelle tâche et choisissez le type de sauvegarde Microsoft SQL.

,Traduisez le contenu ci-dessus en français. Le contenu à traduire, ne traduisez pas la valeur de l"attribut href de la balise HTML a et la valeur de l"attribut src de la balise img. Le contenu entre {{}} n"a pas besoin d"être traduit. Ne traduisez pas ou ne modifiez pas les structures HTML et leurs attributs.

2. Cliquez sur Nom du périphérique strong> pour spécifier l"instance SQL et les bases de données à sauvegarder. Vous pouvez sélectionner une ou plusieurs bases de données selon vos besoins.

3. Sélectionnez un stockage Cible pour enregistrer vos sauvegardes SQL. Vous pouvez spécifier un chemin local ou un chemin réseau.

4. Configurez la Planification pour exécuter la sauvegarde de la base de données SQL quotidiennement, hebdomadairement ou mensuellement, et choisissez la méthode de sauvegarde comme Sauvegarde complète, incrémentielle ou différentielle.

Cliquez sur Démarrer la sauvegarde pour créer et exécuter la tâche. Une fois terminé, vous le trouverez dans l"onglet Tâche de sauvegarde.

✍Plus de fonctionnalités utiles :

• Nettoyage des sauvegardes vous aide à supprimer automatiquement les anciennes versions de sauvegarde et ainsi économiser de l"espace de stockage.
• Les Notifications par e-mail vous permettent de recevoir des notifications par e-mail en cas d"anomalie ou de réussite de la tâche.
• Archiver vous aide à archiver les fichiers de sauvegarde dans un stockage AWS S3.

◉ Comment restaurer plusieurs bases de données à partir d"une sauvegarde :

1. Cliquez sur Tâche de sauvegarde dans la barre de menu de gauche, recherchez la tâche que vous souhaitez restaurer, puis cliquez sur -> Restaurer.

2. Sélectionnez une version de sauvegarde à partir de laquelle vous souhaitez restaurer les bases de données SQL.

3. Ensuite, sélectionnez l"emplacement cible où vous souhaitez effectuer la restauration. Il existe deux options:

• Restaurer à l"emplacement d"origine (par défaut): récupération sur place qui peut revenir aux anciennes versions des bases de données SQL.
• Restaurer à un nouvel emplacement: sélectionnez une autre cible pour effectuer la récupération hors espace. Vous pouvez spécifier le nom de la nouvelle base de données et modifier l"emplacement de stockage.

Écraser la base de données avec le même nom : Si vous cochez cette option, les bases de données sauvegardées écraseront les bases de données cibles portant les mêmes noms. Si vous ne la cochez pas, les bases de données cibles avec les mêmes noms seront omises lors de la restauration.

4. Après tous les paramètres, cliquez sur Démarrer la restauration pour commencer le processus de restauration et attendez patiemment qu"il se termine. Vous pouvez cliquer sur le bouton pour voir les détails de la restauration ou l"annuler.

✍Notes:

• "Restaurer à l"emplacement d"origine" doit confirmer que l"emplacement d"origine existe. Sinon, vous ne pouvez sélectionner que "Restaurer à un nouvel emplacement".
• "Restaurer à l"emplacement d"origine" écrasera ou supprimera les données de la base de données d"origine, s"il y a des données importantes dans la base de données d"origine, il est recommandé de choisir "Restaurer à un nouvel emplacement".

Conclusion

La GUI de restauration de SSMS vous permet de restaurer un seul base de données à la fois. Si vous souhaitez restaurer plusieurs bases de données en une seule fois dans SQL Server, l"approche la plus courante consiste à utiliser un script T-SQL. Cependant, cela impose des restrictions strictes sur les noms de fichiers de sauvegarde et rend difficile la réalisation d"opérations telles que la restauration de bases de données vers une autre instance. Par conséquent, vous pouvez également essayer le logiciel de sauvegarde SQL - AOMEI Cyber Backup.

Cela rend l"opération beaucoup plus facile. Par exemple, vous pouvez sauvegarder automatiquement les bases de données SQL en quelques clics simples et restaurer plusieurs bases de données en une seule fois grâce à une opération tout aussi simple.