Restauration rapide de bases de données

Vous êtes en charge d’un projet s’appuyant sur plusieurs bases de données, dont une base de fichiers (FILESTREAM) ? C’est une corvée de devoir supprimer, puis remonter toutes ces bases pour faire une démonstration avec des données « propres » à un client ? Alors voici une petite astuce qui pourrait vous rendre service.

Au préalable, vous devez lancer SQL Server Management Studio, puisque c’est avec cet outil que tout se passe.

Etape 1 : suppression de la base actuelle

Faites un clic-droit sur la base que vous voulez supprimer, puis cliquez sur… « Supprimer ». Une fenêtre s'ouvre alors.

Cochez la case « Fermer les connexions existantes ». Cela aura pour effet de supprimer la base, même si un processus IIS y est encore connecté. A vous de juger si cette action drastique est adéquate et que personne n'utilise l'application à ce moment-là...

Attention ! N’ayez pas le réflexe de cliquer sur « OK » ! C’est là qu’entre en jeu toute la subtilité de l’astuce…

En haut de la fenêtre, cliquez sur « Script », ou sur la petite flèche située à sa droite afin de choisir l’option qui vous convient le mieux. Ici, on va choisir « Action de script dans une nouvelle fenêtre de requête ».

Fermez la fenêtre ou cliquez sur Annuler. Vous devriez obtenir un nouvel onglet avec un script « .sql » avec ces commandes :

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'MaBase'
GO
USE [master]
GO
ALTER DATABASE MaBase SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE MaBase
GO

Il se peut que la commande « ALTER DATABASE MaBase SET SINGLE_USER WITH ROLLBACK IMMEDIATE » soit absente si la base que vous aviez ciblée n'était pas en cours d’utilisation au moment de la génération du script. Dans ce cas, ajoutez-la à la main.

Etape 2 : restauration de la nouvelle base

Faites un clic-droit sur « Bases de données », puis cliquez sur « Restaurer la base de données… ». Choisissez la base d’origine, la base de destination, etc.

Puis, là aussi, ne cliquez pas sur « OK », mais cliquez à nouveau sur « Script » ou sur la petite flèche afin de choisir « Presse-papiers » (plus pratique dans ce cas, puisqu’on a déjà un onglet avec un fichier .sql).

Fermez la fenêtre, collez le contenu du presse-papier si vous avez procédé ainsi, puis admirez votre script.

RESTORE DATABASE [MaBase] FROM  DISK = N'E:\MesBackups\MaBase_20160503.bak' WITH  FILE = 1,  MOVE N'MaBase' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MaBase.mdf',  MOVE N'MaBase_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MaBase.LDF',  NOUNLOAD,  STATS = 5
GO

Etape 3 (optionnelle) : réduire la taille des fichiers de données et journaux dans la base de données spécifiée

Pour assaisonner le tout, j’ajoute une commande permettant de supprimer les fichiers de données et journaux de mes bases. Si nous ne sommes pas en production, ce sont quelques Mo, voire Go, qui peuvent être économisés sur nos disques durs.

Pas de génération assistée dans ce cas :

ALTER DATABASE MaBase SET RECOVERY SIMPLE
GO
DBCC SHRINKDATABASE (MaBase)
GO

Etape finale : idem pour les autres bases

Renouvelez l’opération pour toutes vos bases et mettez les scripts les uns à la suite des autres pour obtenir un fichier .sql que vous n’aurez qu’à exécuter en appuyant sur F5 pour supprimer, monter et « shrinker » vos bases.

Voici un exemple avec deux bases (dont une base fichier suffixée « _doc ») :

-- Suppression de MaBase :
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'MaBase'
GO
USE [master]
GO
ALTER DATABASE MaBase SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE MaBase
GO

-- Restauration de MaBase :
RESTORE DATABASE [MaBase] FROM  DISK = N'E:\MesBackups\MaBase_20160503.bak' WITH  FILE = 1,  MOVE N'MaBase' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MaBase.mdf',  MOVE N'MaBase_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MaBase.LDF',  NOUNLOAD,  STATS = 5
GO

-- Shrink de MaBase :
ALTER DATABASE MaBase SET RECOVERY SIMPLE
GO
DBCC SHRINKDATABASE (MaBase)
GO


-- Suppression de MaBase_doc :
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'MaBase_doc'
GO
USE [master]
GO
ALTER DATABASE MaBase_doc SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE MaBase_doc
GO

-- Restauration de MaBase_doc :
RESTORE DATABASE [MaBase_doc] FROM  DISK = N'E:\MesBackups\MaBase_doc_20160503.bak' WITH  FILE = 1,  MOVE N'MaBase_doc' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MaBase_doc.mdf',  MOVE N'MaBase_doc_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MaBase_doc.LDF',  NOUNLOAD,  STATS = 5
GO

-- Shrink de MaBase_doc :
ALTER DATABASE MaBase_doc SET RECOVERY SIMPLE
GO
DBCC SHRINKDATABASE (MaBase_doc)
GO

Quelques ultimes précisions…

Si vous changez de version de SQL Server, il faudra mettre à jour le chemin du répertoire « DATA » utilisé lors de l’étape de restauration. Soit en utilisant la fonctionnalité décrite plus haut, soit en cherchant à la main le nouveau chemin via l’Explorateur Windows, soit en retrouvant vous-même le numéro de version (http://sqlserverbuilds.blogspot.fr/).

Et bien sûr, si vous voulez monter un backup plus récent, pensez à changer le nom des bases directement dans le script.

Bonne restauration !

Le blog