SQL Server permet de sauvegarder une partie de la base de données par fichier ou par groupe de fichiers. Mais qu’en est-il de la restauration ? Est-il possible de restaurer partiellement une base ? En imaginant une table mère dans un fichier et sa table fille dans l’autre comment assurer l’intégrité des données avec de telles sauvegardes ?
En fait il n’est pas possible de restaurer partiellement une base de données. Une base de données étant un tout cohérent, on ne peut que la restaurer en entier. La notion de restauration partielle souvent mentionné pour d’autres SGBDR n’est en fait pas du tout une restauration de base, mais une simple extraction des données d’une ou plusieurs tables ne permettant pas de respecter l’intégrité de la base (de toute façon, certaines bases de données comme MySQSL se foutent éperdument de l’intégrité référentielle dont les clauses FOREIGN KEY sont purement cosmétiques !).
Comme il n’est pas possible d’effectuer une restauration partielle avec SQL Server, et ce afin de garantir l’intégrité de la base, le serveur exige au moins une restauration transactionnelle finale destiné à s’assurer que l’ensemble des transactions sont au même point dans tous les fichiers.
Démonstration…
1 РCr̩ation de la base multi fichiers, multi groupe de fichiers :
ON PRIMARY (NAME = 'DB_TEST_IR_PRIM',
FILENAME = 'c:\DATA\DB_TEST_IR_PRIM.mdf',
SIZE = 50 MB),
FILEGROUP FG_CLIENT
(NAME = 'DB_TEST_IR_CLIENT',
FILENAME = 'c:\DATA\DB_TEST_IR_CLIENT.ndf',
SIZE = 50 MB),
FILEGROUP FG_FACTURE
(NAME = 'DB_TEST_IR_FACTURE',
FILENAME = 'c:\DATA\DB_TEST_IR_FACTURE.ndf',
SIZE = 50 MB)
LOG ON (NAME = 'DB_TEST_IR_TRAN',
FILENAME = 'c:\DATA\DB_TEST_IR_TRAN.ldf',
SIZE = 50 MB);
GO
À ce stade nous avons une base constituée de 3 groupes de fichiers ayant chacun 1 fichier de données en sus du fichier du journal des transactions.
2 – Création de deux tables liées par l’intégrité référentielle
GO
CREATE TABLE T_CLIENT
(CLI_ID INT PRIMARY KEY,
CLI_NOM VARCHAR(32))
ON FG_CLIENT;
CREATE TABLE T_FACTURE
(FAC_ID INT PRIMARY KEY,
CLI_ID INT FOREIGN KEY REFERENCES T_CLIENT (CLI_ID),
FAC_DATE DATE)
ON FG_FACTURE;
Notez que la table des clients a été stockée dans le groupe de fichiers FG_CLIENT et celle des factures dans le groupe de fichiers FG_FACTURE.
3 РInitialisons une premi̬re sauvegarde compl̬te
TO DISK = 'c:\DATA\DB_TEST_IR.bak';
Celle sauvegarde initiale est nécessaire pour pouvoir effectuer une sauvegarde partielle ou transactionnelle.
4 Рinsertion de donn̩es des clients et factures
VALUES (1, 'DUPONT'), (2, 'MARTIN');
INSERT INTO T_FACTURE
VALUES (100, 1, '2014-01-16'), (101, 1, '2014-01-18');
Il y a maintenant deux clients dont l’un possède 2 factures.
5 – Sauvegarde du groupe de fichiers contenant les clients
FILEGROUP = 'FG_CLIENT'
TO DISK = 'c:\DATA\DB_TEST_IR_FG_CLIENT.bak';
Cette sauvegarde contient donc nos deux clients et rien d’autres (pas leur facture). Notez la présence du mot clef FILEGROUP. Si nous avions voulu sauvegarder un fichier il aurait fallut lui substituer FILE et indiquer le FILENAME (nom logique du fichier).
6- Insérons maintenant 2 nouveaux clients avec chacun une facture
VALUES (3, 'JOHNSON'), (4, 'SCHMIDT');
INSERT INTO T_FACTURE
VALUES (102, 3, '2014-01-20'), (103, 4, '2014-01-22');
5 – Sauvegarde du groupe de fichiers contenant les factures
FILEGROUP = 'FG_FACTURE'
TO DISK = 'c:\DATA\DB_TEST_IR_FG_FACTURE.bak';
Nous avons maintenant une sauvegarde qui contient des factures pour lesquels les clients correspondant n’ont pas été sauvegardé.
6 Рpar s̩curit̩ effectuons une sauvegarde finale transactionnelle
TO DISK = 'c:\DATA\DB_TEST_IR.trn';
LA RESTAURATION
Nous allons enchainer la restauration initiale complète, notre première partielle contenant les clients et notre seconde partielle contenant les factures. Toutes les restaurations intermédiaires doivent être effectuées en mode NORECOVERY sous peine de placer la base en production après la restauration et interdire le passage d’une restauration complémentaire. Seule le dernier fichier à restaurer doit être passé en mode RECOVERY afin de mettre la base en production…
Restauration de la base complète :
FROM DISK = 'c:\DATA\DB_TEST_IR.bak'
WITH MOVE 'DB_TEST_IR_PRIM'
TO 'c:\DATA\DB_TEST_IR_PRIM2.mdf',
MOVE 'DB_TEST_IR_CLIENT'
TO 'c:\DATA\DB_TEST_IR_CLIENT2.ndf',
MOVE 'DB_TEST_IR_FACTURE'
TO 'c:\DATA\DB_TEST_IR_FACTURE2.ndf',
MOVE 'DB_TEST_IR_TRAN'
TO 'c:\DATA\DB_TEST_IR_TRAN2.ldf',
NORECOVERY;
GO
Notez la présence des MOVE / TO destinés à déplacer les fichiers pour éviter d’écraser les fichiers de la base actuelle (qui ne se serait pas laissé marcher sur les pieds…) ainsi que le changement de nom desdits fichiers.
Restauration du groupe de fichiers FG_CLIENT :
FROM DISK = 'c:\DATA\DB_TEST_IR_FG_CLIENT.bak'
WITH MOVE 'DB_TEST_IR_PRIM'
TO 'c:\DATA\DB_TEST_IR_PRIM2.mdf',
MOVE 'DB_TEST_IR_CLIENT'
TO 'c:\DATA\DB_TEST_IR_CLIENT2.ndf',
MOVE 'DB_TEST_IR_FACTURE'
TO 'c:\DATA\DB_TEST_IR_FACTURE2.ndf',
MOVE 'DB_TEST_IR_TRAN'
TO 'c:\DATA\DB_TEST_IR_TRAN2.ldf',
NORECOVERY;
GO
Restauration du groupe de fichiers FG_FACTURE :
FROM DISK = 'c:\DATA\DB_TEST_IR_FG_FACTURE.bak'
WITH MOVE 'DB_TEST_IR_PRIM'
TO 'c:\DATA\DB_TEST_IR_PRIM2.mdf',
MOVE 'DB_TEST_IR_CLIENT'
TO 'c:\DATA\DB_TEST_IR_CLIENT2.ndf',
MOVE 'DB_TEST_IR_FACTURE'
TO 'c:\DATA\DB_TEST_IR_FACTURE2.ndf',
MOVE 'DB_TEST_IR_TRAN'
TO 'c:\DATA\DB_TEST_IR_TRAN2.ldf',
RECOVERY;
GO
À ce stade, aucun message d’erreur n’est apparu et si nous ne prenons pas garde il semblerait que tout aille bien…
Mais… il suffit de jeter un coup d’œil à l’arborescence des bases de données du serveur pour s’apercevoir que quelque chose ne va pas !
La base de données apparait en cours de restauration dans l’IHM SSMS de sql server
Quel est l’explication ? Il suffit de regarder la panneau résultat de l’interface SQL Server Management Studio pour être informé de la raison de l’état de cette base…
Message d’information de restauration SQL Server indiquant que la base ne sera pas en production du fait des restauration partielles
Voici ce que dit le panneau résultat :
Impossible de récupérer la base de données car le journal n’a pas été restauré.
Impossible de récupérer la base de données car le journal n’a pas été restauré.
Le point de départ de restauration par progression se trouve maintenant au numéro séquentiel dans le journal 35000000047900001. Une restauration par progression supplémentaire au-delà du numéro séquentiel dans le journal 35000000053100001 s’impose pour terminer la séquence de restauration.
Cette instruction RESTORE a réussi à effectuer certaines actions, mais il n’est pas possible de mettre la base de données en ligne parce qu’une ou plusieurs étapes RESTORE sont nécessaires. Les messages précédents expliquent les raisons de l’échec de la récupération à ce stade.
En gros, les restaurations ont bien été effectuée, mais la base est restée en état de restauration. Normal puisque les références transactionneles sont différentes entre les deux fichiers (LSN ou Log Segment Number). Le RECOVERY final de la dernière sauvegarde à donc tout simplement été ignoré…
Ce n’est pas grave… Il suffit maintenant de passer la sauvegarde transactionnelle finale :
FROM DISK = 'c:\DATA\DB_TEST_IR.trn'
WITH MOVE 'DB_TEST_IR_PRIM'
TO 'c:\DATA\DB_TEST_IR_PRIM2.mdf',
MOVE 'DB_TEST_IR_CLIENT'
TO 'c:\DATA\DB_TEST_IR_CLIENT2.ndf',
MOVE 'DB_TEST_IR_FACTURE'
TO 'c:\DATA\DB_TEST_IR_FACTURE2.ndf',
MOVE 'DB_TEST_IR_TRAN'
TO 'c:\DATA\DB_TEST_IR_TRAN2.ldf',
RECOVERY;
GO
C’est terminé, le tour est joué, notre base est restaurée, intègre et en production…
Expert S.G.B.D relationnelles et langage S.Q.L
Moste Valuable Professionnal Microsoft SQL Server
Société SQLspot : modélisation, conseil, formation,
optimisation, audit, tuning, administration SGBDR
Enseignant: CNAM PACA, ISEN Toulon, CESI Aix en Prov.
L’entreprise SQL Spot
Le site web sur le SQL et les SGBDR
