La création d’une base de données SQL Server à l’aide de SQL Server Management Studio (SSMS) est une tâche courante pour les administrateurs de bases de données. Cependant, il peut y avoir des situations où vous devez créer un script pour automatiser le processus. Dans cet article, nous explorerons différents exemples de création d’une base de données SQL Server à l’aide de scripts.
Commande simple pour créer une base de données SQL Server
La manière la plus simple de créer une base de données SQL Server est d’utiliser l’instruction CREATE DATABASE. Cette instruction crée une base de données avec les paramètres par défaut, tels que l’emplacement des fichiers par défaut et le propriétaire étant le login que vous utilisez.
-- créer la base de données MyDatabase
CREATE DATABASE [MyDatabase]
Créer une base de données SQL Server et spécifier les emplacements des fichiers
Si vous souhaitez avoir plus de contrôle sur la configuration de votre base de données, vous pouvez spécifier les emplacements physiques des fichiers, les tailles initiales des fichiers et les incréments de croissance automatique. Cela vous permet de personnaliser la base de données selon vos besoins spécifiques.
-- créer la base de données MyDatabase et spécifier les emplacements physiques des fichiers, les tailles initiales des fichiers et les incréments de croissance automatique
CREATE DATABASE [MyDatabase]
ON (NAME = N'MyDatabase', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\MyDatabase.mdf', SIZE = 1024MB, FILEGROWTH = 256MB)
LOG ON (NAME = N'MyDatabase_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\MyDatabase_log.ldf', SIZE = 512MB, FILEGROWTH = 125MB)
GO
Créer une base de données SQL Server et changer le propriétaire de la base de données
Par défaut, le propriétaire de la base de données est le login que vous utilisez pour exécuter l’instruction CREATE DATABASE. Cependant, vous pouvez facilement changer le propriétaire de la base de données pour un autre login en utilisant l’instruction ALTER AUTHORIZATION.
-- créer la base de données MyDatabase et spécifier les emplacements physiques des fichiers, les tailles initiales des fichiers et les incréments de croissance automatique, changer le propriétaire en sa
CREATE DATABASE [MyDatabase]
ON (NAME = N'MyDatabase', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\MyDatabase.mdf', SIZE = 1024MB, FILEGROWTH = 256MB)
LOG ON (NAME = N'MyDatabase_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\MyDatabase_log.ldf', SIZE = 512MB, FILEGROWTH = 125MB)
GO
-- changer le propriétaire en sa
USE [MyDatabase]
GO
ALTER AUTHORIZATION ON DATABASE::[MyDatabase] TO [sa]
GO
Créer une base de données SQL Server et définir le modèle de récupération
Le modèle de récupération détermine la façon dont le journal des transactions est géré et si la récupération à un moment donné est possible. Par défaut, le modèle de récupération est défini sur “complet” pour la base de données modèle. Cependant, vous pouvez le changer en “simple” en utilisant l’instruction ALTER DATABASE.
-- créer la base de données MyDatabase et spécifier les emplacements physiques des fichiers, les tailles initiales des fichiers et les incréments de croissance automatique, changer le propriétaire en sa, et définir le modèle de récupération sur simple
CREATE DATABASE [MyDatabase]
ON (NAME = N'MyDatabase', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\MyDatabase.mdf', SIZE = 1024MB, FILEGROWTH = 256MB)
LOG ON (NAME = N'MyDatabase_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\MyDatabase_log.ldf', SIZE = 512MB, FILEGROWTH = 125MB)
GO
-- changer le propriétaire en sa
USE [MyDatabase]
GO
ALTER AUTHORIZATION ON DATABASE::[MyDatabase] TO [sa]
GO
-- définir le modèle de récupération sur simple
ALTER DATABASE [MyDatabase] SET RECOVERY SIMPLE
GO
Créer une base de données SQL Server et changer le niveau de compatibilité
Le niveau de compatibilité détermine la façon dont certaines fonctionnalités et comportements de SQL Server sont gérés. Vous pouvez définir le niveau de compatibilité sur une version inférieure de SQL Server en utilisant l’instruction ALTER DATABASE.
-- créer la base de données MyDatabase et spécifier les emplacements physiques des fichiers, les tailles initiales des fichiers et les incréments de croissance automatique, changer le propriétaire en sa, et définir le niveau de compatibilité sur une version inférieure
CREATE DATABASE [MyDatabase]
ON (NAME = N'MyDatabase', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\MyDatabase.mdf', SIZE = 1024MB, FILEGROWTH = 256MB)
LOG ON (NAME = N'MyDatabase_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\MyDatabase_log.ldf', SIZE = 512MB, FILEGROWTH = 125MB)
GO
-- changer le propriétaire en sa
ALTER AUTHORIZATION ON DATABASE::[MyDatabase] TO [sa]
GO
-- définir le modèle de récupération sur simple
ALTER DATABASE [MyDatabase] SET RECOVERY SIMPLE
GO
-- changer le niveau de compatibilité
ALTER DATABASE [MyDatabase] SET COMPATIBILITY_LEVEL = 110
GO
Script de création de base de données SQL Server avec toutes les options
Si vous souhaitez avoir un script qui inclut toutes les options possibles pour créer une base de données, vous pouvez décommenter les autres instructions ALTER DATABASE dans le script fourni ci-dessous.
-- créer la base de données MyDatabase et spécifier les emplacements physiques des fichiers, les tailles initiales des fichiers et les incréments de croissance automatique, changer le propriétaire en sa, et définir le niveau de compatibilité sur une version inférieure
CREATE DATABASE [MyDatabase]
ON (NAME = N'MyDatabase', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\MyDatabase.mdf', SIZE = 1024MB, FILEGROWTH = 256MB)
LOG ON (NAME = N'MyDatabase_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\MyDatabase_log.ldf', SIZE = 512MB, FILEGROWTH = 125MB)
GO
-- changer le propriétaire en sa
ALTER AUTHORIZATION ON DATABASE::[MyDatabase] TO [sa]
GO
-- définir le modèle de récupération sur simple
ALTER DATABASE [MyDatabase] SET RECOVERY SIMPLE
GO
-- changer le niveau de compatibilité
ALTER DATABASE [MyDatabase] SET COMPATIBILITY_LEVEL = 130
GO
/*
-- décommenter et modifier les autres instructions ALTER DATABASE au besoin
ALTER DATABASE [MyDatabase] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [MyDatabase] SET ANSI_NULLS OFF
GO
ALTER DATABASE [MyDatabase] SET ANSI_PADDING OFF
GO
ALTER DATABASE [MyDatabase] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [MyDatabase] SET ARITHABORT OFF
GO
ALTER DATABASE [MyDatabase] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [MyDatabase] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [MyDatabase] SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF)
GO
ALTER DATABASE [MyDatabase] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [MyDatabase] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [MyDatabase] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [MyDatabase] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [MyDatabase] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [MyDatabase] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [MyDatabase] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [MyDatabase] SET DISABLE_BROKER
GO
ALTER DATABASE [MyDatabase] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [MyDatabase] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [MyDatabase] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [MyDatabase] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [MyDatabase] SET READ_WRITE
GO
ALTER DATABASE [MyDatabase] SET RECOVERY FULL
GO
ALTER DATABASE [MyDatabase] SET MULTI_USER
GO
ALTER DATABASE [MyDatabase] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [MyDatabase] SET TARGET_RECOVERY_TIME = 60 SECONDS
GO
ALTER DATABASE [MyDatabase] SET DELAYED_DURABILITY = DISABLED
GO
USE [MyDatabase]
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = Off;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = Primary;
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = On;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = Primary;
GO
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = Off;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = Primary;
GO
*/
Enregistrer le script de création de base de données dans SQL Server
Pour enregistrer le script et l’exécuter dans SQL Server Management Studio, suivez ces étapes :
- Cliquez sur Fichier et choisissez Enregistrer sous…
- Choisissez un répertoire pour stocker le script
- Donnez un nom significatif au fichier
- Enregistrez le fichier
- Pour exécuter le script, cliquez sur le bouton Exécuter ou appuyez sur la touche F5
- Cliquez sur le signe moins sur le dossier Bases de données pour réduire la liste déroulante
- Cliquez sur le bouton Actualiser pour reconstruire la liste des bases de données
- Cliquez sur le signe plus sur le dossier Bases de données pour développer la liste déroulante et afficher la nouvelle base de données
- Pour afficher les propriétés de la nouvelle base de données, cliquez avec le bouton droit sur le nom de la base de données et cliquez sur l’onglet Propriétés
- L’onglet Fichiers affiche le nom de la base de données, le propriétaire, les noms de fichiers logiques et physiques, les tailles de fichiers et les incréments de croissance automatique
- L’onglet Options montre où nous avons changé le modèle de récupération en “Simple” et le niveau de compatibilité
En utilisant des scripts pour créer des bases de données SQL Server, vous pouvez automatiser le processus et avoir plus de contrôle sur la configuration. Que vous ayez besoin d’une base de données simple ou d’une base de données avec des paramètres spécifiques, les scripts offrent flexibilité et efficacité.