MS SQL - Commandes SQL pour gérer un serveur

1. Gerer les paramètres serveur via T-SQL
2. Gestion des bases de données via T-SQL
   • Création d'une BDD configurée en mode recovery SIMPLE 
   • Création d'une BDD - gestion de fichier : increase & shrink
   • Create BDD - Add Table - Insert Data - Checkpoint - LOg Backup - Truncate
   • Create BDD avec plusieurs Filegroups
   • Create BDD - Add Filegroup to BDD - Create Table in filegroup
3. Caractères de séparation dans un fichier à importer
4. Export de données dans un fichier via BCP
5. Import de données dans une table via BCP
6. Bulk Insert de données dans une table
7. Détacher & Attacher une BDD via T-SQL
8. Transfert d'une BDD entre serveurs via backup/restore via T-SQL
9. Gestion de TempDB via T-SQL
10. Créer une alerte

Retour...

-- Récuperer la configuration de votre server MS SQL via T-SQL
--
USE master;
GO
SELECT * FROM sys.configurations;
GO


-- Récuperer la configuration de vos bases MS SQL via T-SQL
--
--
-- Pour les bases de données systèmes
use master
select * from sys.databases
Use tempdb
select * from sys.database_files
Use model
select * from sys.database_files
Use msdb
select * from sys.database_files
-- Remplacer Your-Database-Name par le nom de votre Base de données
Use Your-Database-Name
select * from sys.database_files


-- Modifier la taille de la mémoire RAM maximum utilisable par les bases de votre instance via T-SQL
--
--
USE [master]
GO
-- Activation de l'affichage des options avancées 
EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
-- Limitation de la taille RAM à 1024 Mo (1 gigaoctet de RAM) :
EXEC sys.sp_configure N'max server memory (MB)', N'1024'
GO
RECONFIGURE WITH OVERRIDE
GO
-- Désactivation de l'affichage des options avancées 
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO

​​Retour...

--  Création d'une BDD configurée en mode recovery SIMPLE 
--

-- Remplacer MyBDD par le nom de la base de données que vous souhaitez utiliser pour faire vos tests
-- Remplacer le chemin D:\Your_BDDs\DATAs par celui où vous aller placer vos fichiers de bases de données
-- Remplacer le chemin D:\Your_BDDs\LOGs par celui où vous aller placer vos fichiers journaux
-- Remplacer Your-Database-DATAFILE par le nom à donner à votre fichier de base de données
-- Remplacer Your-Database-LOGFILE par le nom à donner à votre fichier de journaux de transactions
--
--
USE master;
GO
IF EXISTS(SELECT * FROM sys.databases WHERE name = 'MyBDD')
DROP DATABASE MyBDD;
GO
CREATE DATABASE MyBDD ON  PRIMARY 
(  NAME = N'MyBDD', 
   FILENAME = N'D:\Your_BDDs\DATAs\Your-Database-DATAFILE.mdf' , 
   SIZE = 5MB, 
   FILEGROWTH = 1024KB 
)
 LOG ON 
( NAME = N'MyBDD_log', 
  FILENAME = N'D:\Your_BDDs\LOGs\Your-Database-LOGFILE.ldf' , 
  SIZE = 1MB, 
  FILEGROWTH = 10%
);
GO
ALTER DATABASE MyBDD SET RECOVERY SIMPLE;
GO



-- Création d'une BDD - gestion de fichier : increase & shrink


-- Création d'une BDD et vérification via T-SQL
--
--
-- Remplacer MyBDD par le nom de la base de données que vous souhaitez utiliser pour faire vos tests
-- Remplacer aussi MyBDD_01 et MyBDD_02 par le nom que vous souhaitez donner à vos fichiers de base de données
-- Remplacer aussi MyBDD_Log par le nom que vous souhaitez donner à votre fichier des journaux de transactions
-- Remplacer le chemin D:\MyBDD\DATAs par celui où vous aller placer vos fichiers de bases de données
-- Remplacer le chemin D:\MyBDD\LOGs par celui où vous aller placer vos fichiers journaux
--
--
-- Suppression de la BDD si existe déjà
USE master;
GO
IF EXISTS(SELECT * FROM sys.databases WHERE name = 'MyBDD')
DROP DATABASE MyBDD;
GO

--  Création de la base Via T-SQL :
USE master;
GO
CREATE DATABASE MyBDD
ON 
( NAME = MyBDD_01, 
  FILENAME = 'D:\MyBDD\DATAs\MyBDD_01.mdf',   
  SIZE = 50MB, MAXSIZE = 400MB,  
  FILEGROWTH = 20% 
)
LOG ON
( NAME = MyBDD_Log,
  FILENAME = 'D:\MyBDD\LOGs\MyBDD_Log.ldf',  
  SIZE = 10MB, 
  MAXSIZE = UNLIMITED,  
  FILEGROWTH = 10MB 
);
GO
--  Vérifier les bases de données et leur état à l'aide de T-SQL :
SELECT database_id, name, state_desc  FROM sys.databases;
GO
--  Vérifier les fichiers utilisés par la nouvelle base de données de filiale :
USE MyBDD;
GO
SELECT file_id,
  name, 
  size as SizeInPages,
  FILEPROPERTY(name, 'SpaceUsed') as SpaceUsedInPages,
  physical_name
FROM sys.database_files;
GO
SELECT *
FROM sys.database_files;
GO

-- Ajoutez un nouveau fichier à la base de données créée précédemment :
USE master;
GO
ALTER DATABASE MyBDD
  ADD FILE ( NAME = N'MyBDD_02', 
             FILENAME = N'D:\MyBDD\DATAs\MyBDD_02.ndf' , 
             SIZE = 5MB , 
             FILEGROWTH = 1MB 
            ); 
GO


--  Agrandir les fichiers de la BDD :
USE master;
GO
ALTER DATABASE MyBDD MODIFY FILE ( NAME = N'MyBDD_01', SIZE = 70000KB );
GO
ALTER DATABASE MyBDD MODIFY FILE ( NAME = N'MyBDD_02', SIZE = 20000KB );
GO
ALTER DATABASE MyBDD MODIFY FILE ( NAME = N'MyBDD_Log', SIZE = 18000KB );
GO


--  Tentez de réduire la taille des fichiers de données : 
USE MyBDD;
GO
DBCC SHRINKFILE (N'MyBDD_01' , 25);
GO
-- (nouvelle taille = 25 Mo pour le fichier MyBDD_01)
DBCC SHRINKFILE (N'MyBDD_02' , 4);
GO
-- (nouvelle taille = 4 Mo pour le fichier MyBDD-02)
--  Tentez de réduire la taille du fichier des journaux de transaction : 
DBCC SHRINKFILE (N'MyBDD_Log' , 10);
GO
--  Via l'Explorateur d'objets de SQL Management Studio, sélectionnez Rapports
--  puis Rapports standard et Utilisation du disque. Notez la nouvelle taille des fichiers. 


--  Ensuite, essayez de supprimer le fichier de données ajouté MyBDD_02.
USE MyBDD;
GO
ALTER DATABASE MyBDD REMOVE FILE MyBDD_02;
GO
--  Notez que vous recevez une erreur indiquant que le fichier ne peut pas être 
--  supprimé car il n'est pas vide.


--  Exécutez DBCC SHRINKFILE pour vider le fichier et réessayez ensuite de le supprimer :
USE MyBDD;
GO
DBCC SHRINKFILE (N'MyBDD_02' , EMPTYFILE);
GO
ALTER DATABASE MyBDD REMOVE FILE MyBDD_02;
GO
--  Notez que cette fois cela fonctionne
--  Pour vérifier que le fichier a été supprimé :
USE MyBDD
GO
SELECT file_id,
       name, 
       size as SizeInPages,
       FILEPROPERTY(name, 'SpaceUsed') as SpaceUsedInPages,
       physical_name
FROM sys.database_files;
GO



-- Create BDD - Add Table - Insert Data - Checkpoint - LOg Backup - Truncate
-

-- Creation et gérer quelques aspects basiques d'une BDD (insertion, sauvegarde, troncature log) à l'aide de T-SQL
-- 
-- Remplacer MyBDD par le nom que vous souhaitez utiliser pour faire vos tests (nom de la BDD et des fichiers de la BDD)
-- Remplacer le chemin D:\MyBDDs\DATAs par celui où vous aller placer vos nouveaux fichiers de bases de données
-- Remplacer le chemin D:\MyBDDs\LOGs par celui où vous aller placer vos fichiers journaux
-- Remplacer le chemin F:\MyBDD_BACKUPs_Folder par celui où vous aller placer vos sauvegardes
-- Remplacer le nom de table YourExempleTable par celui que vous souhaitez utiliser
-- NB : le mode de récupération est basé sur la valeur actuelle de la base de données model.
--
-- Étape 1 :suppression de la BDD si existe déjà
USE master;
GO
IF EXISTS(SELECT * FROM sys.databases WHERE name = 'MyBDD')
DROP DATABASE MyBDD;
GO
-- Étape 2 :création de la BDD
CREATE DATABASE MyBDD ON PRIMARY 
( NAME = MyBDD_01, 
  FILENAME = N'D:\MyBDD\DATAs\MyBDD_01.mdf',   
  SIZE = 10MB, 
  FILEGROWTH = 20% 
)
LOG ON
( NAME = MyBDD_Log,
  FILENAME = N'D:\MyBDD\LOGs\MyBDD_Log.ldf',  
  SIZE = 2MB, 
  FILEGROWTH = 1MB 
);
GO

-- Étape 2 : définissez la base de données en mode de récupération complète. Noter que le mode de récupération complète 
--         ne prend effet qu'après la première sauvegarde complète de base de données, car les sauvegardes de journal nécessitent une base de sauvegarde. 
ALTER DATABASE MyBDD SET RECOVERY FULL;
GO
-- Étape 3 : exécutez une sauvegarde complète de base de données.
BACKUP DATABASE MyBDD
  TO DISK = 'F:\MyBDD_BACKUPs_Folder\MyBDD_Full-02.bak'
WITH INIT;
GO
-- Étape 4 : créez une table dans la base de données.
USE MyBDD;
GO
CREATE TABLE YourExempleTable
( YourExempleTableId int IDENTITY(1,1) PRIMARY KEY,
  FirstLargeColumn nvarchar(600),
  BigIntColumn bigint
);
GO
-- Étape 5 : examinez les propriétés des fichiers de base de données 
-- Noter que le fichier journal est de type 1 dans sys.databases
SELECT name AS Name,
       size * 8 /1024. as SizeinMB,  
       FILEPROPERTY(name,'SpaceUsed') * 8 /1024. as SpaceUsedInMB,
       CAST(FILEPROPERTY(name,'SpaceUsed') as decimal(10,4))
         / CAST(size as decimal(10,4)) * 100 as PercentSpaceUsed
FROM sys.database_files;
GO
--  Étape 6 : insérer des données (10 000 lignes)
SET NOCOUNT ON;
INSERT INTO YourExempleTable (FirstLargeColumn,BigIntColumn)
  VALUES('Ceci est un test pour insertion',55799);
GO 5000
-- Étape 7 : réexaminez les propriétés du fichier journal de base de données.
-- Noter que la taille du  fichier journal a considérablement augmentée
SELECT name AS Name,
       size * 8 /1024. as SizeinMB,  
       FILEPROPERTY(name,'SpaceUsed') * 8 /1024. as SpaceUsedInMB,
       CAST(FILEPROPERTY(name,'SpaceUsed') as decimal(10,4))
         / CAST(size as decimal(10,4)) * 100 as PercentSpaceUsed
FROM sys.database_files
WHERE type = 1;
GO
-- Étape 8 : créer un point de contrôle. 
--         Notez que la commande CHECKPOINT peut être utilisée pour demander à SQL Server
--         d'effectuer un point de contrôle au lieu d'attendre la prochaine 
--         occurrence où il se produit automatiquement.
CHECKPOINT;
GO
-- Étape 9 : réexaminer les propriétés du fichier journal de base de données.
-- Noter que le point de contrôle n'a apporté aucune modification en termes de taille 
-- -> aucune troncation n'a été effectuée.
SELECT name AS Name,
       size * 8 /1024. as SizeinMB,  
       FILEPROPERTY(name,'SpaceUsed') * 8 /1024. as SpaceUsedInMB,
       CAST(FILEPROPERTY(name,'SpaceUsed') as decimal(10,4))
         / CAST(size as decimal(10,4)) * 100 as PercentSpaceUsed
FROM sys.database_files
WHERE type = 1;
GO
--Étape 10 : Vérifier ce qui empêche la troncation du journal.
-- Faire défiler et noter la colonne log_reuse_wait_desc pour la base de données MyBDD.
-- --> Elle affichera LOG_BACKUP comme motif
SELECT name, log_reuse_wait_desc FROM sys.databases;
GO
--Étape 11 : exécuter une sauvegarde de fichier journal.
BACKUP LOG MyBDD
  TO DISK = 'F:\MyBDD_BACKUPs_Folder\MyBDD_tr.bak'
WITH INIT;
GO
-- Étape 12 : examiner l'utilisation du fichier journal. Le fichier journal doit avoir été tronqué 
-- et de l'espace doit avoir été libéré 
-- ATTENTION : la taille du fichier LDF n'est pas réduite pour autant dans l'explorateur Windows !!!
SELECT name AS Name,
       size * 8 /1024. as SizeinMB,  
       FILEPROPERTY(name,'SpaceUsed') * 8 /1024. as SpaceUsedInMB,
       CAST(FILEPROPERTY(name,'SpaceUsed') as decimal(10,4))
         / CAST(size as decimal(10,4)) * 100 as PercentSpaceUsed
FROM sys.database_files
WHERE type = 1;
GO
--  Pour réduire la taille du fichier des journaux de transaction : 
USE MyBDD;
GO
DBCC SHRINKFILE (N'MyBDD_Log' , 2);
GO
GO



-- Create BDD avec plusieurs Filegroups
-

-- Création d'une BDD répartie dans différents Filegroups via T-SQL
--
--
-- Remplacer MyBDD par le nom  que vous souhaitez utiliser pour faire vos tests
-- Ce nom est utilisé pour celui de la BDD et des fichiers de la BDD dans cette exemple
-- Remplacer FILEGROUP02 et FILEGROUP03 par des noms de Filegroup que vous souhaitez utiliser pour faire vos tests
--
--  
USE master;
GO
IF EXISTS(SELECT 1 FROM sys.databases WHERE name = N'MyBDD')
  DROP DATABASE MyBDD;
GO

CREATE DATABASE MyBDD
ON 
( NAME = MyBDD_data, 
  FILENAME = 'D:\MyBDD\DATAs\MyBDD.mdf',   
  SIZE = 10MB, MAXSIZE = 20MB,  
  FILEGROWTH = 5MB 
)
LOG ON
( NAME = MyBDD_log,
  FILENAME = 'D:\MyBDD\LOGs\MyBDD.ldf',  
  SIZE = 10MB, 
  MAXSIZE = UNLIMITED,  
  FILEGROWTH = 20MB 
);
GO
ALTER DATABASE MyBDD
ADD FILEGROUP FILEGROUP02;
GO
ALTER DATABASE MyBDD 
ADD FILE 
( NAME = MyBDD_data_1, 
  FILENAME = 'D:\MyBDD\DATAs\MyBDD_1.ndf',   
  SIZE = 10MB, 
   FILEGROWTH = 5MB
) TO FILEGROUP FILEGROUP02;
GO
ALTER DATABASE MyBDD 
ADD FILE 
( NAME = MyBDD_data_2, 
  FILENAME = 'D:\MyBDD\DATAs\MyBDD_2.ndf',   
  SIZE = 10MB, 
  MAXSIZE = 20MB,  
  FILEGROWTH = 5MB
) TO FILEGROUP FILEGROUP02;
GO
ALTER DATABASE MyBDD
ADD FILEGROUP FILEGROUP03;
GO
ALTER DATABASE MyBDD 
ADD FILE 
( NAME = MyBDD_data_3, 
  FILENAME = 'D:\MyBDD\DATAs\MyBDD_3.ndf',   
  SIZE = 10MB, 
  MAXSIZE = 20MB,  
  FILEGROWTH = 5MB 
) TO FILEGROUP FILEGROUP03;
GO
ALTER DATABASE MyBDD 
ADD FILE 
( NAME = MyBDD_data_4, 
  FILENAME = 'D:\MyBDD\DATAs\MyBDD_4.ndf',   
  SIZE = 50MB, 
  MAXSIZE = 100MB,  
  FILEGROWTH = 10MB 
) TO FILEGROUP FILEGROUP03;
GO
ALTER DATABASE MyBDD MODIFY FILEGROUP FILEGROUP02 DEFAULT;
GO
-- Pour supprimer la base à la fin de vos test :
USE master;
GO
SELECT 1 FROM sys.databases WHERE name = N'MyBDD'
  DROP DATABASE MyBDD;
GO




-- Create BDD - Add Filegroup to BDD - Create Table in filegroup
- 

-- Creer une base puis ajoutez un groupe de fichiers et un fichier de données à cette base de données via T-SQL :
-- Créer des tables en choississant le Filegroup dans lequel la table sera créée
--
--
-- Remplacer MyBDD par le nom de base de données que vous souhaitez utiliser pour faire vos tests
-- Remplacer aussi Your-Database-File-01 et Your-Database-File-02 par le nom que vous souhaitez donner à vos fichiers de base de données
-- Remplacer aussi Your-LOGFILE-File-01 par le nom que vous souhaitez donner à votre fichier des journaux de transactions
-- Remplacer le chemin D:\MyBDD\DATAs par celui où vous aller placer vos nouveaux fichiers de bases de données
-- Remplacer le chemin D:\MyBDD\LOGs par celui où vous aller placer vos fichiers journaux
-- Remplacer NewFilegroupName par un nom de Filegroup que vous souhaitez utiliser pour faire vos tests
-- Remplacer les noms de table MyTable et MyTable02 par ceux que vous souhaitez utiliser
--
--
-- 
-- Suppression de la BDD si existe déjà
USE master;
GO
IF EXISTS(SELECT * FROM sys.databases WHERE name = 'MyBDD')
DROP DATABASE MyBDD;
GO

--  Création de la base Via T-SQL :
USE master;
GO
CREATE DATABASE MyBDD
ON 
( NAME = MyBDD_01, 
  FILENAME = 'D:\MyBDD\DATAs\Your-Database-File-01.mdf',   
  SIZE = 50MB, MAXSIZE = 400MB,  
  FILEGROWTH = 20% 
)
LOG ON
( NAME = MyBDD_02,
  FILENAME = 'D:\MyBDD\LOGs\Your-LOGFILE-File-01.ldf',  
  SIZE = 10MB, 
  MAXSIZE = UNLIMITED,  
  FILEGROWTH = 10MB 
);
GO 
-- Ajout d'un nouveau Filegroup
ALTER DATABASE MyBDD
ADD FILEGROUP NewFilegroupName;
GO
-- Ajout d'un nouveau fichier dans le nouveau Filegroup
ALTER DATABASE MyBDD 
ADD FILE 
( NAME = MyBDD_dat_2, 
  FILENAME = 'D:\MyBDD\DATAs\Your-Database-File-02.ndf',   
  SIZE = 10MB, 
  MAXSIZE = 20MB,  
  FILEGROWTH = 1MB 
) TO FILEGROUP NewFilegroupName;
GO
-- Créez une nouvelle table par défaut afin de voir dans quel Filegroup elle sera créée par défaut:
-- Vous pouvez adapter les noms, le nombre et le type de colonne selon votre guise (IDdeProduit, NomDuProduit, PrixAchat, PrixVente)
USE MyBDD;
GO
CREATE TABLE dbo.MyTable
( IDdeProduit int IDENTITY(1,1) NOT NULL,
  NomDuProduit nvarchar(128) NOT NULL,
  PrixAchat money NOT NULL,
  PrixVente money NOT NULL
);
GO
--  Vérifiez le groupe de fichiers dans lequel la table a été créée :
SELECT i.data_space_id, 
       s.name AS Filegroup
FROM sys.indexes AS i
INNER JOIN sys.data_spaces AS s
ON i.data_space_id = s.data_space_id 
WHERE i.object_id = OBJECT_ID(N'dbo.MyTable');
GO
--  La table a été créée dans le groupe de fichiers PRIMARY, car ce groupe de fichiers 
--  a été déclaré comme le groupe de fichiers par défaut de la base de données. Notez qu'il est préférable que le groupe de fichiers PRIMARY soit utilisé 
--  uniquement pour les objets système, et pas les données utilisateur, lorsque plusieurs groupes de fichiers sont utilisés dans la base de données.
 
--  Supprimez la table et créez-la dans le groupe de fichiers NewFilegroupName :
USE MyBDD;
GO
DROP TABLE dbo.MyTable; 
GO
CREATE TABLE dbo.MyTable
( IDdeProduit int IDENTITY(1,1) NOT NULL,
  NomDuProduit nvarchar(128) NOT NULL,
  PrixAchat money NOT NULL,
  PrixVente money NOT NULL
) ON NewFilegroupName;
GO
--  Vérifiez le groupe de fichiers dans lequel la table a été créée :
SELECT i.data_space_id, 
       s.name as Filegroup
FROM sys.indexes AS i
INNER JOIN sys.data_spaces AS s
ON i.data_space_id = s.data_space_id 
WHERE i.object_id = OBJECT_ID(N'dbo.MyTable');
GO
-- Changez le groupe de fichiers par défaut :
USE MyBDD;
GO
ALTER DATABASE MyBDD MODIFY FILEGROUP NewFilegroupName DEFAULT;
GO
--  Créez une nouvelle table sans spécifier de groupe de fichiers et vérifiez l'emplacement où il a été créé :
-- Vous pouvez adapter les noms, le nombre et le type de colonne selon votre guise 
USE MyBDD;
GO
CREATE TABLE dbo.MyTable02
( ContactID int  NOT NULL,
  Titre nvarchar(8) NULL,
  Prénom nvarchar(128) NOT NULL,
  Nom nvarchar(128) NOT NULL,
  Email nvarchar(50) NULL
);
GO
--  Notez que la table a été créée dans NewFilegroupName, qui est désormais le nouveau groupe de fichiers par défaut :
SELECT i.data_space_id, 
       s.name as Filegroup
FROM sys.indexes AS i
INNER JOIN sys.data_spaces AS s
ON i.data_space_id = s.data_space_id 
WHERE i.object_id = OBJECT_ID(N'dbo.MyTable02');
GO

​​Retour...

Remarque : L'indicateur de fin de ligne peut être le même que celui employé en tant qu'indicateur du dernier champ. Cependant, un indicateur de fin de ligne distinct s'avère généralement plus utile. Par exemple, afin d'obtenir en sortie des tabulations, marquez la fin du dernier champ de chaque ligne par le caractère de saut de ligne (\n) et la fin de tous les autres champs par le caractère de tabulation (\t). Pour placer chaque enregistrement de données dans une ligne séparée dans le fichier de données, attribuez la combinaison de caractères \r\n à l'indicateur de fin de ligne.

​Retour...

Pour effectuer une exportation en bloc des données de type char ou nchar en utilisant un indicateur de fin différent de celui défini par défaut, il faut le préciser dans la commande bcp. Pour cela, il y a 2 méthodes :

• En utilisant un fichier de format qui précise l'indicateur de fin pour chaque champ.

• Sinon, il faut préciser les indicateurs en argument de votre commande BCP : 

• -t :  définit l'indicateur de fin de ligne pour tous les champs à l'exception du dernier champ de la ligne
• -r : définit l'indicateur de fin de ligne.

Ou bien utiliser un commutateur de format caractère (-c ou -w) sans le commutateur -t, qui définit l'indicateur de fin de champ sur la tabulation, \t (option est équivalente à la spécification de -t\t).

Attention : Si vous ajoutez le commutateur -n (correspondant aux données natives) ou -N (format natif Unicode), les indicateurs de fin ne sont alors pas insérés.

Si une commande bcp interactive contient l'option in ou out sans le commutateur du fichier de format (-f) ou celui du fichier de données (-n, -c, -w ou -N) mais que vous n'avez pas non plus précisé la longueur du préfixe et la longueur de champ, la commande vous invite alors à saisir l'indicateur de fin de champ de chaque champ, tout en vous proposant par défaut de ne pas en spécifier :

Enter field terminator [none]:

Exemples d'exportation de données :

bcp MyBDD.Mytable out C:\myExportFile.txt -c -t, -r \n -T

Cette commande exporte au format de caractères les données de la tableMyBDD.Mytable dans le fichier de données C:\myExportFile.txt en utilisant la virgule (,) comme indicateur de fin de champ et le saut de ligne (\n) comme indicateur de fin de ligne. 
Noter ici que out indique de copier dans un fichier à partir d'une table ou d'une vue de la base de données. Si vous spécifiez un fichier existant, il est remplacé. Lors de l'extraction des données, notez que l'utilitaire bcp représente une chaîne vide comme une chaîne Null, et une chaîne Null comme une chaîne vide.

bcp "SELECT MyTableID, ColName2, ColName3, ColName4 FROM MyBDD.Mytable ORDER BY MyTableID" queryout C:\myExportFile.dat -c -t, -r \r\n -S YourSQLServer\YourInstanceName -T

Cette commande exporte au format de caractères les colonnes MyIdTable, ColName1, ColName2, Colname3 et ColName4 de la table MyTable dans le fichiers de données C:\ExportFile.dat en utilisant la virgule (,) comme indicateur de fin de champ et le saut de ligne ou retour chariot (\r\n) comme indicateur de fin de ligne à partir du serveur SQL YourSQLServer\YourInstanceName.
in copie à partir d'un fichier dans une table ou une vue de base de données.
Noter ici que queryout indique de copier à partir d'une requête. Il doit être spécifié uniquement lors d'une copie de données en bloc à partir d'une requête.


Exemples d'exportation de formats :

bcp MyBDD.dbo.MyTable format nul -c -t, -r \r\n -f C:\MyTableFormat.fmt -T

Cette commande créé un fichier de format nommé MyTableFormat.fmt reposant sur le format de caractères. Ce fichier sera utilisé ultérieurement pour l'importation afin d'éviter d'avoir à fournir les caractères de séparation et autre iundications pour l'importation.
A noter que format est utilisé ici pour indiquer de créer un fichier de format basé sur l'option d'exportation spécifiée (-n, -c, -w ou -N) et les délimiteurs de la table ou de la vue. Lors d'une copie en bloc de données, la commande bcp peut se référer à un fichier de format, ce qui permet d'éviter de ressaisir les informations de format de manière interactive. L'option format nécessite l'option -f ; la création d'un fichier de format XML nécessite également l'option -x. Vous devez spécifier nul comme valeur (format nul).

Retour...

Exemple :

bcp MyBDD.dbo.MyTable in C:\MyExportFile.txt -c -t , -r \n -T

Dans cet exemple, nous importons en bloc le fichier de données C:\MyExportFile.txt en utilisant la virgule (,) comme indicateur de fin de champ et le saut de ligne (\n) comme indicateur de fin de ligne. 

Retour...

Bulk Insert dans une table sans utiliser de fichier de format :

Nous disposons d'un fichier d'import au format CSV MyExportDta.dat . Dans ce fichier, la séparation des champs est fait par virgule et les saut de lignes de type retour chariot\caratères de saut de lignes.

BULK INSERT MyBD.dbo.MyTable
FROM 'C:\MyExportFile.dat'
WITH
  (
    DATAFILETYPE = 'char',
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\r\n'
  );

DATAFILETYPE indique que les do,nnées du fichier à importer sont de type caractères et non de type binaire
FIELDTERMINATOR  permet de définbir la caractère de séparation de champs
ROWTERMINATOR permet de définbir la caractère de fin de ligne


Bulk Insert dans une table en utilisant un fichier de format :

BULK INSERT MyBD.dbo.MyTable
FROM 'C:\MyExportFile.dat'
WITH
  (
    FORMATFILE = 'C:\MyTableFormat.fmt'
  );


Bulk Insert dans une table en utilisant un fichier de format via INSERT INTO et OPENROWSET :
(disponible depuis SQL 2005)
​
INSERT INTO
  MyBD.dbo.MyTable
SELECT *
FROM
  OPENROWSET(BULK 'C:\MyExportFile.dat',
  FORMATFILE = 'C:\MyTableFormat.fmt'
  ) AS e;

 
Exemple complet :

-- Bulk Insert from CSV File via T-SQL
--
-- Préparer vos 2 fichiers d'importation en vous inspirant des exemples fournis pour la syntaxe :
---- Le fichier exemple Your-Import-File.xml définit la structure des données qui seront importés (colonnes, collation,...)
---- Le fichier exemple Your-Import-File.cvs contient les données de chaque ligne à importer
-- Remplacer Your-Database-Name par le nom de la base de données que vous souhaitez utiliser pour faire vos tests
-- Remplacer Your-Table-Name  par le nom que vous souhaitez donner à la table dans lequel les données seront importer
-- Remplacer les noms de colonnes utilisés en exemple pour la table par les votres - Vopus pouvez zn ajouter ou en supprimer selon
-- votre convenance mais n'oublier pas d'adapter les fichiers d'importation en conséquence
-- Remplacer le chemin D:\Your_BDDs\Import par celui où vous aller placer vos fichiers d'importation 
--
--
USE Your-Database-Name;
GO
IF EXISTS(SELECT 1 FROM sys.tables WHERE name = N'Your-Table-Name')
  DROP TABLE dbo.Your-Table-Name;
GO

CREATE TABLE dbo.Your-Table-Name
( Your-Table-Name-ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
  Code-Devise char(3) NOT NULL,
  Taux-de-change decimal(18, 5) NOT NULL,
  Date-Operation-Change datetime NOT NULL
);
GO
TRUNCATE TABLE Your-Table-Name;
GO

BULK INSERT dbo.Your-Table-Name
FROM 'D:\Your_BDDs\Import\Your-Import-File.csv' 
WITH ( FORMATFILE='D:\Your_BDDs\Import\Your-Import-File.xml',
       BATCHSIZE=1000,
       FIRSTROW=2
     );
GO

​Exemple de fichier de format XML

Retour...

-- A utiliser pour transferer une base d'un serveur vers un autre :
-- Une autre méthode expliquée dans le paragraphe suivant consiste à transférer une base entre serveurs via Backup/restore
--
-- Remplacer MyBDD par le nom de la base de données que vous souhaitez utiliser pour faire vos tests
-- Remplacer le chemin D:MyBDDDATAs par celui où vous aller placer vos fichiers de bases de données sur le nouveau serveur
-- Remplacer le chemin D:MyBDDLOGs et D:MyBDD_NewPathLOGs par ceux  où vous aller placer vos fichiers journaux sur le nouveau serveur
-- Remplacer le chemin D:MyBDDDATAs et D:MyBDD_NewPathDATAs par ceux où vous aller placer vos fichiers de bases de données sur le nouveau serveur
-- Remplacer le chemin D:MyBDDLOGs et D:MyBDD_NewPathLOGs par ceux  où vous aller placer vos fichiers journaux sur le nouveau serveur
-- Remplacer aussi MyBDD.mdf et  par le nom de votre fichier de base de données
-- Remplacer aussi MyBDD_log.ldf par le nom de votre fichier des journaux de transactions
--
--
-- Supprimer la BDD si elle existe déjà pour un précédent test 
USE master;
GO
IF EXISTS(SELECT * FROM sys.databases WHERE name = 'MyBDD')
DROP DATABASE MyBDD;
GO
-- Créer la base de données de test pour vérifier le mécanisme
CREATE DATABASE MyBDD ON  PRIMARY 
( NAME = N'MyBDD', 
  FILENAME = N'D:MyBDDDATAsMyBDD.mdf' , 
  SIZE = 10240KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'MyBDD_log', 
  FILENAME = N'D:MyBDDLOGsMyBDD_log.ldf' , 
  SIZE = 5120KB , FILEGROWTH = 10%);
GO
---- Détacher une BDD sur un serveur SQL :
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'MyBDD'
GO
----------------------------------------------------------------------------------------------------------------------------------------------------
-- Copier la base (fichiers Bases et fichiers Logs) vers un autre serveur SQL selon une méthode de votre choix (copie réseau, via clé USB,...)
----------------------------------------------------------------------------------------------------------------------------------------------------

-- Rattacher la BDD sur un autre serveur SQL via T-SQL :
USE [master]
GO
CREATE DATABASE [MyBDD] ON 
( FILENAME = N'D:MyBDD_NewPathDATAsMyBDD.mdf' ),
( FILENAME =  N'D:MyBDD_NewPathLOGsMyBDD_log.ldf' )
 FOR ATTACH
GO

​Retour...

-- Deplacer une base de données via sauvegarde/restauration via T-SQL
--
--
-- Remplacer MyBDD par le nom de la bases de données à déplacer via sauvegarde/restauration
-- Remplacer SQL-Server-01 par le nom du serveur SQL sur lequel est fait la sauvegarde
-- Remplacer SQL-Server-02 sur lequel est fait la restauration
-- Remplacer F:\MyBDD_BACKUPs_Folder par le chemin à utiliser pour placer votre sauvegarde
-- Dans l'exemple on considère que la structure de répertoire est la même sur les 2 serveurs 
---- Par exemple F:\MyBDD_BACKUPs_Folder correspond au même chemin où sont placer les sauvegarde
---- Par exemple D:\MyBDD\DATAs\ correspond au même chemin où sont placer les fichiers de données de la BDD
---- Par exemple D:\MyBDD\LOGs\ correspond au même chemin où sont placer les fichiers journaux de la BDD
---- Vous pouvez adapter ces chemins selon votre envoronnement

-- Supprimer la BDD si elle existe déjà si nécessaire
USE master;
GO
IF EXISTS(SELECT * FROM sys.databases WHERE name = 'MyBDD')
DROP DATABASE MyBDD;
GO

-- Creer la BDD de test MyBDD avec les 2 fichiers de BDD :
CREATE DATABASE MyBDD ON  PRIMARY 
( NAME = N'MyBDD1', 
  FILENAME = N'D:\MyBDD\DATAs\FILE-BDD-1.mdf' , 
  SIZE = 8192KB , FILEGROWTH = 1024KB ),
( NAME = N'MyBDD2', 
  FILENAME = N'D:\MyBDD\DATAs\FILE-BDD-2.ndf' , 
  SIZE = 8192KB , FILEGROWTH = 1024KB )
LOG ON 
( NAME = N'MyBDD_log', 
  FILENAME = N'D:\MyBDD\LOGs\MyBDD_log.ldf' , 
  SIZE = 4096KB , FILEGROWTH = 10%);
GO
-- PLacer la base de données en mode de récupération complète. Précisez que le mode de récupération complète prend effet après la première sauvegarde complète 
--         de base de données, car les sauvegardes de journal nécessitent une base. 
ALTER DATABASE MyBDD SET RECOVERY FULL;
GO

-- A faire sur le serveur SQL-Server-01 :
-----------------------------------------
-- Lancement d'une sauvegarde FULL :
Use MyBDD
BACKUP DATABASE [MyBDD] TO  DISK = N'F:\MyBDD_BACKUPs_Folder\MyBDD.bak' WITH NOFORMAT, INIT,  NAME = N'MyBDD-Complète Base de données Sauvegarde', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
-- Création d'une table avec des données.
USE MyBDD;
GO
SET NOCOUNT ON;
CREATE TABLE MyTABLE
( MyTABLEID int IDENTITY(1,1) PRIMARY KEY,
  MyColSTRING nvarchar(600),
  MyColINT bigint
);
-- Ajout de données à la table
DECLARE @Counter int = 0;
WHILE @Counter < 5
BEGIN
  INSERT INTO MyTABLE (MyColSTRING,MyColINT)
    VALUES('Test insertion de données',12345);
  SET @Counter += 1;
END;
GO
-- Lancement d'une sauvegarde DIFFERENTIELLE :
Use MyBDD
BACKUP DATABASE [MyBDD] TO  DISK = N'F:\MyBDD_BACKUPs_Folder\MyBDD.bak' WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N'MyBDD-Différentielle Base de données Sauvegarde', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
-- Ajout de données à la table
DECLARE @Counter int = 0;
WHILE @Counter < 5
BEGIN
  INSERT INTO MyTABLE (MyColSTRING,MyColINT)
    VALUES('Test insertion de données 2ème série',98765);
  SET @Counter += 1;
END;
GO
-- Lancement d'une sauvegarde des journaux de transactions :
Use MyBDD
BACKUP LOG [MyBDD] TO  DISK = N'F:\MyBDD_BACKUPs_Folder\MyBDD.bak' WITH NOFORMAT, NOINIT,  NAME = N'MyBDD-Journal des transactions  Sauvegarde', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
-- Copier le ou les conteneurs de backup vers le second server
-- Ajouter le conteneur de sauvegarde sur le second serveur pour référencer les backups sur ce second serveur



-- A faire sur le serveur SQL-Server-02 :
-----------------------------------------
RESTORE DATABASE [MyBDD] FILE = N'MyBDD1' FROM  DISK = N'F:\MyBDD_BACKUPs_Folder\MyBDD.bak' WITH  FILE = 1,  MOVE N'MyBDD1' TO N'D:\MyBDD\DATAs\FILE-BDD-1.mdf',  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 10
GO
RESTORE DATABASE [MyBDD] FILE = N'MyBDD2' FROM  DISK = N'F:\MyBDD_BACKUPs_Folder\MyBDD.bak' WITH  FILE = 1,  MOVE N'MyBDD2' TO N'D:\MyBDD\DATAs\FILE-BDD-2.mdf',  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 10
GO
RESTORE DATABASE [MyBDD] FILE = N'MyBDD1' FROM  DISK = N'F:\MyBDD_BACKUPs_Folder\MyBDD.bak' WITH  FILE = 2,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO
RESTORE DATABASE [MyBDD] FILE = N'MyBDD2' FROM  DISK = N'F:\MyBDD_BACKUPs_Folder\MyBDD.bak' WITH  FILE = 2,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO
RESTORE LOG [MyBDD] FROM  DISK = N'F:\MyBDD_BACKUPs_Folder\MyBDD.bak' WITH  FILE = 3,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO
RESTORE LOG [MyBDD] WITH  RECOVERY
GO
-- S'assurer que toutes les données ont été restaurées. dbo.MyTABLE doit contenir 2 000 lignes.
USE MyBDD;
GO
SELECT * FROM dbo.MyTABLE;
GO

​​Retour...

-- Gérer TEMPDB via T-SQL :
--
​-- Vous pouvez dérouler ce scénario en séquence pour des tests
--
-- Remplacer le chemin D:\Your_BDDs\DATAs par celui où sont placés vos fichiers DATAs de TempDB
-- Remplacer le chemin D:\Your_BDDs\LOGs par celui où sont placés vos fichiers journaux de TempDB
-- Remplacer les autres noms qui sont ici des exemples de tel que tempdb1 par les noms que vous 
-- souhaitez utiliser pour votre environnements
--
--
-- Déplacement des fichiers TempDB :
------------------------------------------------
USE master;
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'C:\Your_BDDs\DATAs\tempdb1.mdf');
ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'C:\Your_BDDs\LOGs\tempdbLog1.ldf');
GO
-- Relancer l'instance


-- Changer le nom logique des fichiers de BDD de TempDB :
---------------------------------------------------------------------------
USE tempdb;
GO
ALTER DATABASE [tempdb] MODIFY FILE (NAME=N'tempdev', NEWNAME=N'tempdb1')
GO
USE master;
GO
ALTER DATABASE [tempdb] MODIFY FILE (NAME=N'tempLog', NEWNAME=N'tempLog1')
GO

-- Ajouter des fichiers de BDD à TempDB :
-----------------------------------------------------
USE master;
GO
 ALTER DATABASE tempdb 
ADD FILE ( NAME = N'tempdb2', 
FILENAME = N'C:\Your_BDDs\DATAs\tempdb2.ndf' , 
SIZE = 5000KB , FILEGROWTH = 2048KB );
ALTER DATABASE tempdb 
ADD FILE ( NAME = N'tempdb3', 
FILENAME = N'C:\Your_BDDs\DATAs\tempdb3.ndf' , 
SIZE = 5000KB , FILEGROWTH = 2048KB );
ALTER DATABASE tempdb 
ADD FILE ( NAME = N'tempdb4', 
FILENAME = N'C:\Your_BDDs\DATAs\tempdb4.ndf' , 
SIZE = 5000KB , FILEGROWTH = 2048KB );
GO

-- Modifier la taille d'un fichier de BDD de TempDB :
-----------------------------------------------------------------
-- Les noms utllisés ici sont consécutives aux modifications de renommage déjà effectuées
-- Si vous n'avez rien renommé, remplacez Tempdb1 par  Tempdb et Templog1 par TempLog

USE master;
GO
ALTER DATABASE tempdb
  MODIFY FILE ( NAME = tempdb1, SIZE = 64MB );
GO
ALTER DATABASE tempdb
  MODIFY FILE ( NAME = N'tempLog1', SIZE = 24MB );
GO

-- Remettre les anciens noms de TempDB :
------------------------------------------------------
USE tempdb;
GO
ALTER DATABASE [tempdb] MODIFY FILE (NAME=N'tempdb1', NEWNAME=N'tempdev')
GO
USE master;
GO
ALTER DATABASE [tempdb] MODIFY FILE (NAME=N'tempLog1', NEWNAME=N'tempLog')
GO
USE [tempdb]
GO
ALTER DATABASE [tempdb]  REMOVE FILE [tempdb2]
GO
USE [tempdb]
GO
ALTER DATABASE [tempdb]  REMOVE FILE [tempdb3]
GO
USE [tempdb]
GO
ALTER DATABASE [tempdb]  REMOVE FILE [tempdb4]
GO

​Retour...

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Fichier journal des transactions plein', 
@message_id=9002, 
@severity=0, 
@enabled=1, 
@delay_between_responses=0, 
@include_event_description_in=1, 
@notification_message=N'Pb Transaction Log plein !!!  faite un backup des transaction Log et un troncate ', 
@job_id=N'43c450f0-e761-46ad-8acd-5ff699cb1223'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Fichier journal des transactions plein', @operator_name=N'PGX', @notification_method = 1
GO

​​Retour...