En discutant avec un de mes clients, il me demandait s’il fallait forcément supprimer une base de données existante avant de la restaurer. La réponse est évidemment non. En effet, il est possible d’utiliser l’option REPLACE avec la commande RESTORE mais celui-ci se demandait alors s’il y avait au final une différence entre faire une suppression de bases de données via la commande DROP DATABASE suivie d’une restauration  et une restauration directe via la commande RESTORE et l’option REPLACE mis à part une simplification du code à écrire. Il y a en effet une voir plusieurs mais nous n’en traiterons qu’une seule dans ce billet et qui peut ne pas être négligeable dans certains cas.

Il faut se rappeler que la première chose qu’effectue une opération d’une restauration est la création et l’initialisation des fichiers de bases de données. L’utilisation de l’option REPLACE peut nous permettre dans ce cas de passer outre cette phase et accélérer ainsi notre opération de restauration. Avant de passer à la démonstration on va commencer par activer certains traces flag qui vont nous permettre de visualiser les différentes étapes exécutées lors d’une restauration, à savoir :

• 3004 : permet de remonter les informations concernant l’initialisation d’un fichier avec remplissage de zéro.
• 3014 : permet de remonter des informations plus détaillées concernant les opérations de sauvegarde et de restauration effectuées par SQL Server
• 3605 : renvoie des informations de trace détaillées dans le journal des erreurs SQL

DBCC TRACEON(3004, 3014, 3605,  1);
GO;

Créons ensuite une base de données avec un fichier de données de 10GB et un journal des transactions de 1GB et insérons un jeu de données :

CREATE DATABASE testSpeedRestore
ON  PRIMARY
( NAME = N’testSpeedRestore_Data’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\testSpeedRestore.mdf’ , SIZE = 10GB)
LOG ON
( NAME = N’testSpeedRestore_Log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\testSpeedRestore_1.ldf’ , SIZE = 1GB)
GO

USE testSpeedRestore;
GO

CREATE TABLE t1
(
col CHAR(4000) DEFAULT REPLICATE(‘T’, 4000)
);
GO

INSERT t1 DEFAULT VALUES;
GO 100000

Si on jette un coup d’oeil à la volumétrie de la table nous obtenons ceci :

390 Mo de données. Cela devrait être rapide à restaurer  

Initions une sauvegarde de la base de données.

BACKUP DATABASE testSpeedRestore TO DISK = ‘E:\SQL_BACKUP\testSpeedRestore.bak';
GO

1er test : Restauration de la sauvegarde en ayant au préalable supprimer la base de données concernée

USE [master];
GO

ALTER DATABASE testSpeedRestore SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

DROP DATABASE testSpeedRestore;
GO

On recycle ensuite le journal des erreurs SQL Server pour une lecture plus lisible après la restauration. /!\ Cette action est très certainement à proscrire sur un environnement de production /!\

EXEC sp_cycle_errorlog;
GO

On restaure notre base de données.

RESTORE DATABASE testSpeedRestore FROM DISK = ‘E:\SQL_BACKUP\testSpeedRestore.bak';
GO

En visualisant le journal des erreurs SQL on peut voir les opérations détaillées de notre opération de restauration :

La restauration de notre base de données a pris 3min et 21s. On peut remarquer (flèches rouges) que les opérations de création et d’initialisation des fichiers de bases de données (Zeroing completed …) occupent majoritairement la totalité du temps de restauration (environ 3min et 20s), ce qui est normal car il faut initialiser 10GB + 1GB de fichiers de données avant de restaurer seulement 390Mo de données.

 2ème test : Restauration de la sauvegarde en écrasant la base de données existante

Dans ce 2ème test nous allons simplement restaurer notre base de données avec l’option REPLACE mais avant tout recyclons le journal des erreurs SQL Server.

EXEC sp_cycle_errorlog;
GO

USE [master];
GO

RESTORE DATABASE testSpeedRestore FROM DISK = ‘E:\SQL_BACKUP\testSpeedRestore.bak’
WITH REPLACE;
GO

La restauration a pris seulement 33s pour s’exécution sur mon environnement !! Regardons le journal des erreurs SQL Server :

On peut remarquer ici que la phase d’initialisation pour le fichier de données a été skipper. Celui-ci est en réalité réutilisé lors de l’opération de restauration ce qui nous fait gagner énormément de temps pendant cette première phase de création et d’initialisation des fichiers de données. Imaginez de faire cela pour une base de données d’une taille de fichiers d’une centaine de Giga-octets voir plus. Cependant ceci ne concerne que les fichiers de données. En effet, le fichier journal est quant à lui bien réinitialisé avec des zéros et ceci pour des raisons de design de fonctionnement. J’en parlerai lors d’un autre billet si un jour l’occasion se présente.

Pour résumé la restauration d’une base avec l’option REPLACE peut être intéressante dans un aspect purement « vitesse de restauration ». Nous n’avons traité que ce point dans le billet pour rappel. Une autre option qui existe et qui peut être utilisée : INSTANT FILE INITIALIZATION. En octroyant le privilège « Perform Volume Maintenance Tasks » au compte de service SQL on peut également skipper la phase d’initialisation des fichiers pendant la restauration mais aussi pour d’autres opérations mais -ci n’est pas forcément configuré pour diverses raisons (bonnes ou mauvaises) sur les environnements SQL Server.

Bonne restauration !!

David BARBARIN (Mikedavem)
MVP SQL Server