Base de données SQL Server : Créer, modifier, supprimer et restaurer

Qu'est-ce que la base de données?

Une base de données est une collection d'objets tels que les tables, vues, procédures stockées, déclencheurs et fonctions. Dans SQL ServerUne base de données est l'espace organisé où tous ces objets liés sont stockés et interconnectés.

Prenons quelques exemples tirés de la vie réelle :

• Une bibliothèque est l'endroit où l'on range les livres.
• Un foyer, c'est l'endroit où l'on vit.
• Un parking est un endroit où l'on gare des véhicules, et les exemples de ce type sont innombrables.

De même, une base de données sert de conteneur à l'ensemble de nos tables, vues et procédures stockées. Techniquement, une base de données stocke les données de manière organisée pour faciliter leur accès et leur récupération. Dans SQL Server, il existe deux types de bases de données :

• Bases de données système : Ces bases de données sont créées automatiquement lors de l'installation de SQL Server. Elles jouent un rôle crucial dans le serveur, notamment pour garantir le bon fonctionnement des objets de base de données. Parmi les bases de données système de SQL Server, on trouve par exemple master, msdb, model, tempdb et resource.
• Bases de données utilisateurs : Ces bases de données sont créées par des utilisateurs comme vous, qui ont reçu l'autorisation de les créer.

Règles pour créer une base de données

Tout d'abord, vous devez connaître les règles de base pour créer une nouvelle base de données :

• Les noms de bases de données doivent être uniques au sein d'une instance de SQL Server.
• Les noms de bases de données peuvent comporter au maximum 128 caractères.
• L'instruction CREATE DATABASE doit être exécutée en mode de validation automatique.

L'image ci-dessous présente les deux approches disponibles pour créer une base de données dans SQL Server.

Il existe deux façons de créer une base de données dans SQL Server :

• SQL Server Management Studio
• Transact SQL

Comment créer une base de données dans SQL Server Management Studio

Voici la procédure étape par étape pour créer une base de données dans SQL Server Management Studio :

Étape 1) Dans la fenêtre « Explorateur d'objets », cliquez avec le bouton droit sur « Base de données », puis sélectionnez « Nouvelle base de données ».

Le menu contextuel (accessible par clic droit) contenant l'option « Nouvelle base de données » est présenté ci-dessous.

Étape 2) L'écran « Nouvelle base de données » s'affiche. Saisissez un nom pour la base de données. La colonne « Nom logique » est automatiquement remplie avec :

• Edu – Type de fichier : Lignes Données et groupe de fichiers : PRIMAIRE
• Edu_log – Type de fichier : LOG et groupe de fichiers : « Non applicable »

La boîte de dialogue « Nouvelle base de données » avec le nom de la base de données et ses fichiers logiques est affichée ci-dessous.

Ici:

• A) Edu (Type de fichier : Données de lignes, Groupe de fichiers : PRIMAIRE) est le fichier de données .mdf.
• B) Edu_log (Type de fichier : LOG, Groupe de fichiers : 'Non applicable') est le fichier journal .ldf.

Étape 3) (Facultatif) Pour des paramètres plus avancés, accédez aux pages « Options » et « Groupes de fichiers ». Pour les débutants, la création de la base de données depuis l'onglet Général suffit.

Les pages Options et Groupes de fichiers sont affichées ci-dessous.

Étape 4) Cliquez sur « Ajouter » (le bouton OK) pour créer la base de données.

Le bouton permettant de confirmer la création est mis en évidence ci-dessous.

Résultat: La base de données « Edu » est créée. Vous pouvez l'enrichir ; elle contiendra des tables, des vues, etc. Ces éléments sont initialement vides ; vous pouvez les ajouter ultérieurement.

Afficher la requête source : Vous pouvez consulter la requête source de la base de données « Edu » nouvellement créée. Pour ce faire : cliquez avec le bouton droit sur le nom de la base de données > Créer un script de base de données > CRÉER > Nouvelle fenêtre d’éditeur de requêtes.

Fenêtre de requête : Le script CREATE généré s'ouvre dans une nouvelle fenêtre de requête, comme indiqué ci-dessous.

Créer un scénario :

USE [master]
GO

CREATE DATABASE [Edu]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Edu', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL_MS\MSSQL\DATA\Edu.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'Edu_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL_MS\MSSQL\DATA\Edu_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )

Créer une base de données avec T-SQL

Une autre méthode consiste à écrire une requête T-SQL pour créer une base de données, puis à l'exécuter. Examinons la requête T-SQL la plus simple pour créer une base de données.

syntaxe:

CREATE DATABASE <Database_name>

requête:

CREATE DATABASE [Edu_TSQL_file]

Cliquez sur « Exécuter » pour lancer la requête, comme indiqué ci-dessous.

Résultat: Vous pouvez voir le fichier Edu_TSQL_file créé dans l'explorateur d'objets SQL.

Voyons maintenant comment créer une base de données avec des fichiers .mdf et .ldf explicites. Vous pouvez ici spécifier l'emplacement du fichier directement dans la requête.

syntaxe:

CREATE DATABASE database_name   
[ CONTAINMENT = { NONE | PARTIAL } ]  
[ ON   
      [ PRIMARY ] <filespec> [ ,...n ]   
      [ , <filegroup> [ ,...n ] ]   
      [ LOG ON <filespec> [ ,...n ] ]   
];

requête:

CREATE DATABASE [Edu_TSQL_file]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Edu_TSQL_file', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL_MS\MSSQL\DATA\Edu_TSQL_file.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'Edu_TSQL_file_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL_MS\MSSQL\DATA\Edu_TSQL_file_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )

La base de données créée avec ses fichiers de données explicites et ses fichiers journaux apparaît dans l'explorateur d'objets, comme indiqué ci-dessous.

Comment modifier la base de données

Tout comme la requête CREATE DATABASE, vous pouvez également modifier une base de données. Vous pouvez la renommer, changer l'emplacement d'un fichier ou un paramètre, et bien plus encore.

Règles de base pour la modification d'une base de données :

• L'instruction ALTER DATABASE doit être exécutée en mode de validation automatique.
• ALTER DATABASE n'est pas autorisé dans une transaction explicite ou implicite.

Il existe deux façons de modifier une base de données dans SQL Server :

• SQL Server Management Studio
• Transact SQL

Comment modifier la base de données dans SQL Server Management Studio

Voici les étapes à suivre pour modifier une base de données dans SQL Server Management Studio. Prenons l'exemple de la base de données « Edu », que nous avons créée au préalable. Cette base de données est affichée ci-dessous dans l'Explorateur d'objets.

Étape 1) Renommez la base de données. Faites un clic droit sur le nom de la base de données et cliquez sur « Renommer ».

Étape 2) Le nom de la base de données est désormais modifiable. Saisissez le nouveau nom et appuyez sur Entrée.

Résultat: La base de données s'appelle désormais « Edu_Alter » au lieu de « Edu ».

Modifier la base de données avec Transact-SQL

Modifions maintenant une base de données à l'aide de T-SQL.

syntaxe:

ALTER DATABASE <Databse_name>              
MODIFY NAME = <New Name>

requête:

ALTER DATABASE Edu_TSQL
MODIFY NAME = Edu_TSQL_Alter;

Exécutez la requête ci-dessus en cliquant sur « Exécuter ».

Résultat: La base de données a été renommée « Edu_TSQL_Alter » au lieu de « Edu_TSQL », comme indiqué ci-dessous.

Syntaxe générale : La syntaxe complète de ALTER DATABASE prend en charge de nombreuses options, notamment le renommage, le classement, les options de fichier et le niveau de compatibilité.

ALTER DATABASE { database_name  | CURRENT }  
{   MODIFY NAME = new_database_name   
  | COLLATE collation_name  
  | <file_and_filegroup_options>  
  | SET <option_spec> [ ,...n ] [ WITH <termination> ] 
  | SET COMPATIBILITY_LEVEL = { 140 | 130 | 120 | 110 | 100 | 90 }   
} ;

À noter: Les niveaux de compatibilité indiqués ci-dessus correspondent aux versions antérieures. Les versions actuelles ajoutent les niveaux 150 (SQL Server 2019), 160 (SQL Server 2022) et 170 (SQL Server 2025), tout en continuant à prendre en charge plusieurs niveaux antérieurs.

Modifier le nom du fichier .mdf/.ldf

requête:

Alter DATABASE Edu_TSQL_Alter;
MODIFY FILE ( NAME = Edu_TSQL, NEWNAME = Edu_TSQL_newName );

Le fichier logique renommé est reflété dans les propriétés de la base de données, comme indiqué ci-dessous.

Modification de l'emplacement du fichier .mdf/.ldf

requête:

Alter DATABASE Edu_TSQL_Alter;
MODIFY FILE ( NAME = Edu_TSQL_NewName, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL_MS\MSSQL\DATA\New_File\Edu_TSQL_log.ldf' );

Le chemin d'accès mis à jour pour la base de données est indiqué ci-dessous.

Supprimer la base de données

Il existe 2 façons de supprimer (supprimer) une base de données dans SQL Server :

• SQL Server Management Studio
• Transact SQL

Comment supprimer une base de données dans SQL Server Management Studio

Voici la procédure pour supprimer une base de données dans SQL Server Management Studio. Essayons de supprimer notre base de données pré-créée « Edu_Alter ».

Étape 1) Faites un clic droit sur la base de données, cliquez sur « Supprimer », puis sur « OK ».

Résultat: L'élément « Edu_Alter » a été supprimé de la liste de la base de données « Explorateur d'objets ».

Supprimer la base de données à l'aide de Transact-SQL

Essayons de supprimer notre base de données pré-créée 'Edu_TSQL_Alter'.

syntaxe:

DROP DATABASE <Databse_name>

requête:

USE master;  
GO  
DROP DATABASE Edu_TSQL_Alter;  
GO

Exécutez la requête ci-dessus en cliquant sur « Exécuter ».

Résultat: L'élément « Edu_TSQL_Alter » a été supprimé de la liste des bases de données de l'explorateur d'objets, comme indiqué ci-dessous.

Restaurer la base de données dans SQL Server

Vous pouvez créer une base de données en restaurant une base de données que vous aviez sauvegardée précédemment. Pour ce faire, exécutez la commande RESTORE DATABASE, dont la syntaxe est la suivante :

restore Database <database name> from disk = '<Backup file location + filename>

La requête doit être exécutée dans la fenêtre de requête, comme les commandes précédentes. Par exemple :

restore database Edu from disk = 'C:\Backup\Edu_full_backup.bak'

Vous pouvez également utiliser la navigation graphique : cliquez avec le bouton droit sur Base de données > Restaurer la base de données > Périphérique > importer le fichier de sauvegarde > cliquez sur OK.

FAQ