Programme Administration SQL
Chapitre I : Administration sous SQL
1. Introduction
2. Architecture de SQL Server
3. Gestion de la mémoire
4. Paramétrage du serveur
5. Paramétrage des bases de données
6. Création et gestion de login
7. Création des utilisateurs
8. Attribution des permissions
9. Création des alertes
10. Vérification de l’intégrité des bases de données
11. Défragmentation des index et planification de maintenance
Chapitre II : Sauvegarde des bases de données
1. Introduction
2. Différents types de sauvegarde
3. Sauvegarder une base de données
4. Conclusion
Chapitre III : Restauration des bases de données
1. Introduction
2. Mise en œuvre d’une restauration de base de données
3. Les différents types de restauration
4. Conclusion
______________________________________________________________________________
Christian Serge MBERI Tél : 06 975 62 86/05 562 24 46
Page 1 sur 16
Chapitre I : Administration sous SQL
1. Introduction
Pour faciliter l’intégration entre les différentes Données d’une Entreprise, il est
vite apparu que la conception, l’installation et la maintenance des
collections des Données devaient être assurées d’une manière cohérente et
coordonnée. Ce rôle est dévolu à une personne ou une équipe dénommée
« Administration des Données » ou « Administrateur de la Base de Données »,
selon qu’elle supervise l’ensemble des Données ou seulement les Données
d’une base.
Ainsi, une Administrateur de la BD est la personne responsable de la
description (définition) des Structures des Bases de Données.
Les tâches de l’administration de la base sont :
Tâche de conception en aidant les utilisateurs non Informaticiens à
exprimer leurs besoins, à structurer les Données, à définir les procédures
de saisie et de MAJ,
Tâche de création en aidant les Informaticiens dans la mise en œuvre
de ces procédures,
Tâche de maintenance en assurant l’intégrité, la sécurité, l’efficacité
et l’évolution des Données,
Tâche d’arbitre pour résoudre les conflits éventuels entre les différentes
classes d’usagers de la BD.
2. Architecture de SQL Server
L’architecture de SQL Server est représentée dans le schéma ci-
dessous :
______________________________________________________________________________
Christian Serge MBERI Tél : 06 975 62 86/05 562 24 46
Page 2 sur 16
Dans la plupart des SGBD Relationnels dont SQL Server, l’architecture est
composée de :
La couche protocole,
Le moteur relationnel qui s’occupe des commandes T-SQL,
Le moteur de stockage qui gère physiquement les données,
SQL OS qui adresse les ressources de la machine,
Outils : pour la maintenance et la gestion.
3. Gestion de la mémoire
Il s’agit de fixer une limite à la mémoire, parce que SQL Server a
tendance d’occuper toute la mémoire : pour cela il faut aller dans
l’explorateur d’objets, clic droit sur le serveur, propriétés, sélectionner
Mémoire, fixer ensuite votre limite.
4. Paramétrage du serveur
Il s’agit :
de permettre ou pas à SQL Server d’utiliser tous les processeurs :
pour cela il faut aller dans l’explorateur d’objets, clic droit sur le
serveur, propriétés, sélectionner Processeurs. A la commande
Activer les processeurs et ensuite définir automatiquement le
masque d’affinité du processeur ou d’entrée sortie.
de la sécurité : Authentification du serveur. Il y a deux modes
d’authentification, le mode windows et le mode mixte windows
et SQL Server.
des connexions : il est recommandé de laisser les options
sélectionnées par défaut,
paramètres de base de données : il est indiqué dans cette
option de cocher sur la commande « compresser la base de
données ». Vous avez aussi la possibilité de changer
l’emplacement par défaut des données, des journaux et des
sauvegardes.
5. Paramétrage des bases de données
Il s’agit essentiellement d’avoir la possibilité de changer l’emplacement
des données tel que énuméré plus haut.
6. Création et gestion de login
Il s’agit de créer des connexions ou modifier les paramètres des logins
existants. Pour plus de détails, consulter le tutoriel.
7. Création des utilisateurs
______________________________________________________________________________
Christian Serge MBERI Tél : 06 975 62 86/05 562 24 46
Page 3 sur 16
Il s’agit ici de créer des utilisateurs liés aux différents logins. Pour plus de
détails, consulter le tutoriel.
8. Attribution des permissions
Il s’agit d’attribuer ou de retirer des droits aux utilisateurs des tables ou
de toute la base de données.
Les commandes GRANT pour attribuer et REVOKE pour retirer sont
utilisées à cet effet.
Exemple :
USE GestionCommande
GRANT SELECT ON COMMANDE TO Serge
Serge est le nom de l’utilisateur.
Bien entendu l’attribution ou la révocation des droits peut aussi se faire
à l’aide de l’assistant.
9. Création des alertes
10. Vérification de l’intégrité des bases de données
11. Défragmentation des index et planification de maintenance
Chapitre II : Sauvegarde des bases de données
1. Introduction
Sauvegarder une base de données peut paraitre anodin, cependant,
suivant les besoins en production ou en haute disponibilité de la base, les
planifications de sauvegarde d’une base de données ne seront pas les
mêmes. La gestion des sauvegardes reste donc l’une des tâches les plus
importantes dans SQL Server.
En effet, l’exactitude, la rigueur et l’organisation seront vos outils clés pour
réussir des sauvegardes cohérentes, à tel point que ces sauvegardes
pourront être par la suite automatisées grâce à l’agent SQL Server, sous
forme de travaux. Il est donc très important de passer du temps à la
planification de la sauvegarde. On peut voir ce temps passé comme un gain
futur de temps d’administration de la base sauvegardée.
______________________________________________________________________________
Christian Serge MBERI Tél : 06 975 62 86/05 562 24 46
Page 4 sur 16
Pour planifier une sauvegarde de base de données dans de bonne
condition, il est important de se poser les bonnes questions. Ces questions
concernent entre autre les caractéristiques de la base de données à
sauvegarder, l’importance de l’intégrité des données dans la base, ou
encore la disponibilité que doit avoir la base de données lors de la
sauvegarde, autrement dit, est-ce que les utilisateurs pourront ou non
travailler sur cette base lors de la sauvegarde. Autant de questions qu’il
convient de se poser pour effectuer une planification intelligente qui va
engendrer un gain de temps sur la future administration de la base.
Il existe bien entendu plusieurs types de sauvegardes dans SQL Server. Les
meilleures solutions de sauvegardes seront celles qui utiliseront plusieurs types
de sauvegardes pour les bases utilisateurs. En effet, SQL Server propose des
sauvegardes complètes, des sauvegardes des journaux, ou encore des
sauvegardes différentielles. Ces types de sauvegardes peuvent en plus être
appliqués à la base complète ou à un groupe de fichier seul. SQL Server
propose donc plusieurs solutions, combinables, qui au final, nous donnent
une plus grande flexibilité pour nos sauvegardes de bases de données.
2. Différents types de sauvegarde
La sauvegarde de chaque base sera différente dans le sens où
chaque solution de sauvegarde sera composée d’un ou plusieurs
types de sauvegarde suivant plusieurs critères (Taille de la base, niveau
de disponibilité…). Il est donc important de bien mettre en place la
phase de planification de la sauvegarde, afin de faire les bons choix.
Présentons maintenant les différents types de sauvegarde de base de
données.
2.1 Sauvegarde complète de base de données
La sauvegarde totale d’une base de données est le point de
départ de toute planification d’une base de données. Tous les
autres types de sauvegarde ne seront pas possibles, si aucune
sauvegarde totale n’a été faite auparavant. Ne faire que des
sauvegardes totales (ou complètes) est risqué et mène en général
à une perte de temps et d’espace disque assez conséquente. De
plus, les transactions validées ne sont pas retenues pour une
sauvegarde de type complète. On peut donc considérer que les
sauvegardes complètes de base de données sont mieux adaptés
aux bases de faible volume et pour lesquelles il est facile de valider
à nouveau toutes les transactions effectuées sur celle-ci.
______________________________________________________________________________
Christian Serge MBERI Tél : 06 975 62 86/05 562 24 46
Page 5 sur 16
2.2 Sauvegarde du journal de transaction
Les sauvegardes des fichiers journaux peuvent être mises en place en
complément des sauvegardes totales de base de données. Les
avantages principaux des journaux de base de données sont qu’ils
permettent de retrouver la plupart des transactions opérées sur la base
de données, et ils ne prennent pas une place grandissante dans
l’espace de stockage, tout simplement parce qu’il est possible de
tronquer les sauvegardes en fonction des anciennes sauvegardes de
journaux.
2.3 Sauvegarde différentielle d’une base de données
Si toutefois les sauvegardes de journaux peuvent vite devenir
volumineuses, il existe une alternative saine pour palier à ce problème,
les sauvegardes différentielles de base de données. En effet, ce type
de sauvegarde va nous permettre de sauvegarder les données non
______________________________________________________________________________
Christian Serge MBERI Tél : 06 975 62 86/05 562 24 46
Page 6 sur 16
sauvegardées dans la dernière sauvegarde totale. Cette solution est
donc évolutive, rapide et elle permet un gain de place énorme.
2.4 Sauvegarde par groupes de fichiers
Les sauvegardes complètes et les sauvegardes différentielles de base
de données peuvent prendre s’étendre sur la durée lorsqu’il s’agit de
bases de données à gros volumes. Si toutefois votre base de données
est définie sur plusieurs fichiers de données (.mdf), il est possible de
choisir de sauvegarder la base par groupe de fichiers de données.
3. Sauvegarder une base de données
Nous allons maintenant expliquer les différentes manières de sauvegarder
une base de données. Tout d’abord, avant de commencer à sauvegarder, il
faut savoir qu’un utilisateur simple ne peut pas sauvegarder une base, il lui
faut les droits contenus dans les rôles suivants : sysadmin, db_owner,
db_backupoperator.
3.1 Sauvegarde de base de données utilisateur
______________________________________________________________________________
Christian Serge MBERI Tél : 06 975 62 86/05 562 24 46
Page 7 sur 16
Les bases de données utilisateurs sont les bases les plus sujettes à
être sauvegardées dans l’entreprise. Il est important que dans SQL
Server la sauvegarde d’une base de données ne se fasse pas sous
forme de fichier, mais bien sous forme d’unité. On peut alors
énoncer les unités physiques et les unités logiques de sauvegarde.
Définissons ces deux termes :
Unité de sauvegarde physique : Une unité de sauvegarde physique
correspond au nom complet du fichier de sauvegarde dans le
système de fichier Windows. Pour prendre un exemple parlant, si
jamais une opération qui peut entrainer une perte de données est
à faire, il convient d’effectuer une sauvegarde sur une unité
physique de données, autrement dit, un disque.
Unité de sauvegarde logique : Une unité logique de sauvegarde
est en vérité, une unité de sauvegarde physique référencée par un
nom logique dans SQL Server.
Elles se créent directement dans l’explorateur d’objet, en
déployant le nœud objet serveur puis en sélectionnant Nouvelle
unité de sauvegarde dans le menu contextuel du nœud Unités de
sauvegarde. Il suffit alors de donner un nom à cette unité de
sauvegarde et de cliquer sur OK.
______________________________________________________________________________
Christian Serge MBERI Tél : 06 975 62 86/05 562 24 46
Page 8 sur 16
Il est aussi possible de créer une unité de sauvegarde de cet ordre grâce à
une instruction TSQL. Dans ce cas-là, nous allons utiliser une procédure
stockée. Voici la syntaxe de cette procédure :
Voici la signification des arguments suivants :
- @devtype : Le type de support de la sauvegarde physique à référencer.
- @logicalname : Le nom logique de l’unité de sauvegarde logique.
- @physicalname : Le chemin complet de l’unité de sauvegarde physique
référencée par l’unité de sauvegarde logique.
Nous allons maintenant sauvegarder une base de données. Il existe comme
toujours, dans SQL Server, deux manière d’effectuer une même opération.
Nous allons montrer ces deux manières.
______________________________________________________________________________
Christian Serge MBERI Tél : 06 975 62 86/05 562 24 46
Page 9 sur 16
Note : Le mode de récupération de la base n’est pas une option applicable
au moment de la sauvegarde. Il faut le changer au niveau des propriétés de
la base elle même, dans la partie Option. Vous avez le choix entre les modes
de récupération complet, journalisé en bloc et simple.
3.1.1 Avec du code T-SQL (Transact – SQL)
Pour effectuer des sauvegardes de base de données avec
du code T-SQL, nous allons passer par l’intermédiaire de
l’instruction BACKUP. Cette instruction va nous permettre
d’effectuer tous les types de sauvegarde de base de
données. Détaillons les arguments pour les différents types de
sauvegarde. Dans le cas général, on utilise BACKUPDATABASE
pour faire une sauvegarde des données et BACKUP LOG pour
sauvegarder les fichiers de log. TO DISK permet de préciser
l’emplacement de l’unité physique de sauvegarde, alors que
TO tout seul, permet de préciser l’unité logique de
sauvegarde préalablement créée.
- WITH COMPRESSION : Permet de compresser les sauvegardes de données.
- WITH DIFFERENTIAL : Permet de préciser que la sauvegarde est différentielle.
Ce type de sauvegarde peut être utilisé seulement si une sauvegarde
complète a d’abord été faite.
______________________________________________________________________________
Christian Serge MBERI Tél : 06 975 62 86/05 562 24 46
Page 10 sur 16
- WITH MEDIANAME : Permet de donner un nom au groupe de fichier créé.
3.1.2 Avec SSMS (SQL Server Management Studio)
Avec SSMS, la sauvegarde d’une base de données de tout type se fait d’une
même manière. Dans l’explorateur d’objets, développez les nœuds du
serveur, des objets serveur, affichez le menu contextuel du nœud Unité de
sauvegarde et choisissez l’option Sauvegarder une base de données. La
fenêtre suivante apparait :
Les types de sauvegarde complets, différentiels, et de fichiers de log, se
choisissent dans Type de sauvegarde. Pensez à choisir la bonne base dans
l’option Base de données. En revanche, les sauvegardes par groupe de
fichiers se gèrent dans la partie inférieure de la fenêtre. Dans la sous partie
Destination, par défaut, il existe un seul et unique fichier. Si vous voulez
effectuer votre sauvegarde sur plusieurs fichiers, cliquez sur Ajouter, pour
______________________________________________________________________________
Christian Serge MBERI Tél : 06 975 62 86/05 562 24 46
Page 11 sur 16
ajouter un fichier de sauvegarde. Pensez à spécifier le nom du jeu de
sauvegarde. Concernant l’expiration, si celle-ci est à 0, celle-ci n’est pas
active.
3.2 Sauvegarde de base de données système
La sauvegarde des bases de données système sont quasiment toute
aussi important que la sauvegarde de bases de données utilisateur. En effet,
les bases de données système contiennent la définition de toutes les bases
de l’instance.
La base de données Master contient toutes les informations relatives au
bon fonctionnement de SQL Server. La base Master contient aussi la
définition de toutes les connexions et de tous les serveurs liés.
La base de données MSDB contient tous les travaux planifiés, les lots SSIS et
les informations de réplication.
Enfin la base de données Model sert de base de départ pour toute base
de données de type utilisateur.
Note : La sauvegarde de ces bases en production est quand même assez
rare. Ce ne sont donc pas les bases à sauvegarder en priorité. Leur
sauvegarde est donc ponctuelle, il n’est donc pas nécessaire d’opérer à une
planification pour ces sauvegardes.
4. Conclusion
Savoir faire une sauvegarde de base de données ne sert à rien tant que l’on
ne sait pas restaurer une base de données.
______________________________________________________________________________
Christian Serge MBERI Tél : 06 975 62 86/05 562 24 46
Page 12 sur 16
Chapitre III : Restauration des bases de données
1. Introduction
Une opération de restauration de base de données correspond à
l’opération inverse de la sauvegarde de base de données. Celle-ci
peut être faite de deux manières : grâce à l’instruction RESTORE ou par
l’intermédiaire de l’interface graphique dans SSMS. Le fait de replacer
le fichier de données (.mdf) à un emplacement de la machine ne
constitue pas une restauration de base de données. Pour être
utilisable, une base de données doit être inscrite dans la base Master,
qui est une base de données système de SQL Server. Par conséquent,
SQL Server doit être installé sur la machine, et les deux seules manières
de restaurer une base de données sont les deux présentées dans ce
document.
Note : L’application d’une restauration de base de données reste un
événement simple, mais essentiel. Cet évènement peut intervenir dans
les seuls cas d’une demande utilisateur, ou bien lors de l’arrêt d’une
machine. Dans ce dernier cas, on parlera de restauration automatique.
Parlons maintenant des restaurations automatiques. Concrètement,
qu’est ce que c’est ? Une restauration automatique est une procédure
qui intervient lors de chaque redémarrage du serveur. Elle permet de
vérifier si la dernière opération sur la base de données inscrite sur le
journal des transactions possède un point de synchronisation. Si ce
n’est pas le cas, alors tout le journal est relu afin de trouver les points
défectueux dans le fichier de données. Les opérations valides sont
alors ré exécutés et les non valides ne le sont pas.
Au point de vue des restaurations de base de données, et afin de
garantir une non disponibilité des données la plus faible possible, SQL
Server exécute un certain nombre de tâches automatiquement,
toujours dans un souci de gain de temps. Voici les opérations
exécutées automatiquement par SQL Server :
-Contrôle de sécurité : L’intérêt principal de ce contrôle de sécurité est
d’anticiper les restaurations accidentelles de bases de données. Dit
d’une autre manière, de revenir accidentellement à une sauvegarde
antérieure de base de données.
- Reconstruction de base de données : Lors d’une restauration de base
de données, SQL Server s’assure que la base de données et les fichiers
qui s’y rattachent existent bien. Par la suite il sera possible de transférer
les données dans la base et par conséquent, d’écrire sur le fichier de
base de données.
______________________________________________________________________________
Christian Serge MBERI Tél : 06 975 62 86/05 562 24 46
Page 13 sur 16
Enfin, pour finir cette introduction, il y a des opérations préliminaires à
faire pour assurer la bonne exécution d’une restauration de base de
données. Nous allons les présenter tout de suite. Tout d’abord, il faut
assurer une vérification de sauvegarde. Il vous faudra ensuite vérifier
qu’aucun utilisateur n’est présent, et n’utilise la base de données. Vous
pourrez alors restaurer votre base de données de façon optimale. Nous
pouvons maintenant passer à la restauration de la base de données,
dont nous allons présenter les deux manières.
2. Mise en œuvre d’une restauration de base de données
2.1 L’instruction RESTORE
Avec du code T-SQL, c’est l’instruction RESTORE qui va nous
permettre d’effectuer une restauration de base de données. En
voici la syntaxe complète :
2.2 Les options de l’instruction RESTORE
Présentons maintenant les options disponibles avec l’instruction RESTORE.
-RECOVERY : Avec cette option, SQL Server passe en revue tout le journal des
opérations afin de déterminer lesquelles ont étés défectueuses, pour les
annuler.
- NORECOVERY : C’est l’inverse de l’opération RECOVERY.
- FILE : Cette option n’est utile que dans le cas où le fichier de sauvegarde
contient plusieurs sauvegardes. Elle permet de préciser le numéro de la
sauvegarde afin de bien restaurer la bonne sauvegarde.
- MOVE … TO : Cette option permet de placer le fichier de restauration de la
base de données à l’endroit voulu sur le système de fichier Windows.
- REPLACE : Permet de restaurer une base de données en écrasant la base
de données existante.
______________________________________________________________________________
Christian Serge MBERI Tél : 06 975 62 86/05 562 24 46
Page 14 sur 16
- STOPAT : Permet de rejouer la totalité des opérations valides du journal de
transaction jusqu’à une date précisée au format varchar, char,
smalldatetime ou datetime. Cette option n’est disponible que dans le cas où
la base de données est en mode de restauration complète.
- STOPATMARK, STOPBEFOREMARK : Permet de restaurer la base de données
jusqu’à une instruction marquée dans le journal de base de données. Cette
option n’est disponible que dans le cas d’une base de données configurée
en mode de restauration complète.
- CONTINUE_AFTER_ERROR : Elle est le contraire de STOP_ON_ERROR. Permet
de continuer la restauration de la base de données même si une erreur est
levée lors de cette restauration. L’option par défaut est STOP_ON_ERROR.
Les autres options sont moins utilisées, puisqu’elles sont beaucoup plus
techniques. Toute fois, il n’est pas exclu que vous en ayez besoin de temps en
temps.
3. Les différents types de restauration
Suivant le type de sauvegarde que vous aurez faite de votre base de
données, la façon de la restaurer pourra différer. Pour être plus clair dans nos
propos, la façon de restaurer une base de données ne sera pas la même
suivant que la sauvegarde de base de données soit totale ou bien
différentielle. Détaillons les différentes façons de restaurer une base de
données, en prenant en compte que chaque type de sauvegarde peut
permettre de restaurer une base de données de deux manières ; avec SSMS
ou avec du code Transact SQL.
3.1 Restauration à partir d’une sauvegarde complète
La restauration à partir d’une sauvegarde complète est le moyen le plus
rapide d’obtenir un résultat d’une restauration. En effet, c’est ce type de
restauration qui garantit le plus faible temps d’indisponibilité au niveau
serveur. Il est donc conseillé, si les ressources en espace mémoire et si c’est
possible, d’opérer des sauvegardes complètes, afin de garantir une perte de
temps minime au niveau de la disponibilité du serveur de base de données.
Avec du code T-SQL, la syntaxe est très simple. La voici :
Il est juste nécessaire de préciser le nom du fichier de sauvegarde complète.
En revanche, avec SSMS, vous devrez passer par le chemin suivant. Dans
l’explorateur d’objets, faites un clic droit sur la base de données à restaurer.
Dans le menu contextuel, choisissez tache, puis restaurer, puis base de
données. Il est alors évident que pour restaurer une base de données avec
______________________________________________________________________________
Christian Serge MBERI Tél : 06 975 62 86/05 562 24 46
Page 15 sur 16
nos sauvegardes complètes, il faudra choisir « A partir de l’unité » dans la
source de restauration. Cliquez alors sur OK pour valider votre choix.
3.2 Restauration à partir d’une sauvegarde partielle
Comme pour la sauvegarde, ce type de restauration ne peut intervenir que
dans le cas où une restauration à partir d’une sauvegarde complète a été
faite auparavant. Les façons des sauvegarder sont quasi identiques à la
restauration avec un fichier de sauvegarde complète. Un simple
changement interviens dans le cas où l’on restaure avec SSMS : il convient
d’ajouter à la suite de la sauvegarde complète, le fichier de sauvegarde
différentielle dans les sources de données.
3.3 Restauration à partir d’une sauvegarde des fichiers journaux
Le journal de transaction est le dernier élément à restaurer car c’est lui qui va
permettre de vérifier s’il ne manque pas des données. Si plusieurs fichiers
journaux sont à restaurer, ils doivent être restaurés dans l’ordre
chronologique. La restauration des fichiers journaux se fait à partir de
l’instruction T-SQL suivante :
Lors d’une restauration, si vous ne voulez restaurer qu’à partir des fichiers
journaux, à partir de SSMS, il vous suffira de ne sélectionner que les fichiers
journaux en cochant la case à cet effet dans les sources de données de
restauration.
Note : La restauration d’une base de données à partir d’un groupe de fichier
se fait de la même manière. Il suffit juste de préciser toutes les sources de
données à utiliser. S’il n’en manque ne serait ce qu’une seule, la restauration
ne sera pas faite.
4. Conclusion
Dans ce chapitre, vous avez appris à restaurer une base de données de
deux manières, quelle que soit le type de sauvegarde faite en amont. Ce
chapitre vient bien entendu compléter le chapitre de sauvegarde d’une
base de données. Pour plus de détails sur ce cours, il est conseillé de vous
rendre sur la documentation officielle en ligne Microsoft, ou consulter les
tutoriels concernant ce sujet.
______________________________________________________________________________
Christian Serge MBERI Tél : 06 975 62 86/05 562 24 46
Page 16 sur 16
