Créer des captures instantanées de bases de données

Publié le par

Même en phase de développement, il est important de sauvegarder une base de données.
On peut estimer qu’il n’est pas nécessaire de mettre en place une stratégie de sauvegarde, et, comme une erreur arrive vite lors des tests unitaires, il faut que l’on puisse replacer la base de données avec le jeu de données qui a été altéré par un test, sans perdre le travail de plusieurs développeurs.
Si les captures instantanées de bases de données ne permettent pas de protéger complètement l’intégrité d’une base de données, elles sont très utiles si l’on veut se prémunir des erreurs de l’utilisateur pour « restaurer » les valeurs d’une table ou récupérer une procédure stockée …

Tout d’abord, qu’est-ce qu’une capture instantanée de base de données ?
C’est une vue statique, en lecture seule, d’une base de données source.
Dès lors les possibilités offertes par cette fonctionnalité dépassent ce qui est décrit en introduction, et on peut tout à fait s’en servir pour des statistiques, d’autant que tout comme une base de données en ligne, on peut en supprimer les objets.

La commande T-SQL qui permet de créer une capture instantanée de base de données est simple :

1
2
3
CREATE DATABASE maBD_Snapshot
ON (NAME = nomDesFichiersLogiquesDeDonnees, FILENAME = 'C:\maBD_Snapshot.ext'
AS SNAPSHOT OF maBD

Après la création d’une capture instantanée de base de données, vous noterez en lisant de contenu de la table système sys.databases que la colonne source_database_id est valuée seulement pour celle-ci.
Vous verrez également sous Management Studio apparaître la node « Captures instantanées de base de données » sous la node « Bases de données ». En la déployant, vous trouverez la capture que vous venez de réaliser.

Ainsi, pour restaurer les valeurs d’une table de la base de données en ligne avec les valeurs contenues avant une erreur, il suffit d’écrire :

1
2
3
4
5
6
7
---------------------------------
-- Nicolas SOUQUET - 06/04/2009 -
---------------------------------
UPDATE maBD.monSchema.maTable
SET mesColonnes = SNAP.mesColonnes
FROM maBD.monSchema.maTable AS CIBLE
JOIN maBDSnapshot.monSchema.maTable AS SNAP ON CIBLE.PK_ID = SNAP ON.PK_ID

Et pour restaurer la base de données à partir d’une de ses captures instantanées :

1
2
3
USE master  
RESTORE DATABASE maBD
FROM DATABASE_SNAPSHOT = 'maBDSnaphot'

Attention : il est nécessaire que la capture instantanée de données que l’on souhaite restaurer soit la seule capture instantanée de la base de données à restaurer. Dans le cas contraire, on obtiendra l’erreur suivante, qui n’en laisse rien voir :

Msg 3137, Level 16, State 4, Line 1
Impossible de restaurer la base de données. Le nom primaire ou le nom de la capture instantanée a été spécifié de façon incorrecte, toutes les autres captures instantanées ont été supprimées, ou il manque des fichiers.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE s’est terminé anormalement.

Il devient ensuite simple de créer un job qui se chargera de créer régulièrement une capture instantanée de votre base de données. La commande T-SQL de ce job pourrait être :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
---------------------------------
-- Nicolas SOUQUET - 06/04/2009 -
---------------------------------
-- Création d'une nouvelle capture instantanée de base de données
DECLARE @nomSnapshot SYSNAME
SELECT @nomSnapshot = 'maBD_' + REPLACE(REPLACE(CONVERT(CHAR(10), GETDATE(), 103) + '_' + CONVERT(CHAR(8), GETDATE(), 108), '/', '_'), ':', '_')
 
DECLARE @SQL VARCHAR(256)
SET @SQL = 'CREATE DATABASE ' + @nomSnapshot
                + ' ON (NAME = nomDesFichiersLogiquesDeDonneesDeMaBD, FILENAME = ''C:\' + @nomSnapshot + '.snap'')'
                + ' AS SNAPSHOT OF maBD'
 
EXEC (@SQL)
 
-- Suppression des anciennes captures instantanées de base de données
DECLARE curSnapshots CURSOR FOR
    SELECT name
    FROM sys.databases
    WHERE source_database_id IS NOT NULL
    AND name <> @nomSnapshot
FOR READ ONLY
 
OPEN curSnapshots
FETCH NEXT FROM curSnapshots INTO @nomSnapshot
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = 'DROP DATABASE ' + @nomSnapshot
    EXEC (@SQL)
    FETCH NEXT FROM curSnapshots INTO @nomSnapshot
END
 
DEALLOCATE curSnapshots

Il vous suffit ensuite de planifier le job pour voir celui-ci s’exécuter à une fréquence qui vous garantisse un minimum de perte de données.

Enfin, s’il est clair qu’une capture de base de données ne vaut pas une bonne stratégie de sauvegarde de base de données, on peut restaurer une base de données à partir d’une capture de celle-ci :

1
2
RESTORE DATABASE maBD
FROM DATABASE_SNAPSHOT = maBD_Snapshot

Voici un bout de code à placer dans un job pour créer des captures instantanées de toutes les bases de données sauf les bases de données système :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
---------------------------------
-- Nicolas SOUQUET - 06/04/2009 -
---------------------------------
DECLARE @SQL VARCHAR(256)
 
---------------------------------------------------------------------
-- Suppression des anciennes captures instantanées de base de données  
---------------------------------------------------------------------
DECLARE curSnapshots CURSOR FOR  
  SELECT name
  FROM sys.databases  
  WHERE source_database_id IS NOT NULL
  AND create_date < CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)
FOR READ ONLY  
 
DECLARE @nomSnapshot SYSNAME
 
OPEN curSnapshots  
FETCH NEXT FROM curSnapshots INTO @nomSnapshot  
WHILE @@FETCH_STATUS = 0  
BEGIN  
  SET @SQL = 'DROP DATABASE ' + @nomSnapshot  
  EXEC (@SQL)
  FETCH NEXT FROM curSnapshots INTO @nomSnapshot  
END
 
DEALLOCATE curSnapshots
 
--------------------------------------------
-- Captures instantanées de bases de données
--------------------------------------------
DECLARE curBD CURSOR FOR
  SELECT name
  FROM sys.databases
  WHERE source_database_id IS NULL
  AND name NOT IN ('master', 'tempdb', 'model', 'msdb', 'ReportServer', 'ReportServerTempDB')
  AND state_desc = 'ONLINE'
FOR READ ONLY
 
DECLARE @NomBD SYSNAME,
    @ListeFichiers VARCHAR(255)
   
OPEN curBD
FETCH NEXT FROM curBD INTO @NomBD
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @ListeFichiers = NULL
 
  SELECT @ListeFichiers = ISNULL(@ListeFichiers, '') +
                '(NAME = ' + MF.name + ', FILENAME = ''C:\' + MF.name + '.snap'')' + ', '
  FROM sys.master_files AS MF
  JOIN sys.databases AS D ON D.database_id = MF.database_id
  WHERE D.name = @NomBD
  AND MF.type = 0 -- fichiers de données
 
  SELECT @ListeFichiers = SUBSTRING(@ListeFichiers, 1, LEN(@ListeFichiers) - 1)
 
  SET @SQL = 'CREATE DATABASE ' + @NomBD + '_' + REPLACE(REPLACE(CONVERT(CHAR(10), GETDATE(), 103) + '_' + CONVERT(CHAR(8), GETDATE(), 108), '/', '_'), ':', '_')
        + ' ON ' + @ListeFichiers +
        + ' AS SNAPSHOT OF ' + @NomBD
 
  EXEC (@SQL)
  FETCH NEXT FROM curBD INTO @NomBD
END
 
DEALLOCATE curBD

ElSuket

Laisser un commentaire