Blog

Scripter la création d’une base données


Contexte

Dernier projet en date : un client me demande de transformer un mastodonte en “basounette”, autrement dit de faire de l’échantillonnage de données afin de permettre à chaque développeur de disposer de sa propre copie d’une base de données, version light. Voici encore de quoi alimenter leurs interrogations du type : “Bah je ne comprend pas la requête marchait bien avec 10 lignes sur l’environnement de DEV et là on passe en PROD, sur 100 millions de lignes, ça passe plus. C’est quoi le problème ?” Bref, pour réaliser cette tâche d’échantillonnage, on a besoin de développer un algo et de générer une coquille vide de la base de données source. La première partie est bien évidemment la plus complexe et entre deux échantillonnages, mon pote SQL Rudi Bruchez me glisse une commande de la mort qui tue pour prendre en charge la seconde partie sans souci.

DBCC CLONEDATABASE

Selon la documentation, le principe est le suivant : créer une nouvelle base de données de destination qui utilise la même disposition de fichier comme source, mais avec une taille de fichier par défaut… Exactement ce qu’il me faut !

Expert SQL Server - Scripter la création d'une base données - Powershell SQL Server  - bdd_clone

Disponible à partir de SQL Server 2014 SP2, la commande DBCC CLONEDATABASE m’a complètement échappée et surtout bluffée. C’est un peu comme le disque SSD et la fibre optique, on se demande comment on a pu s’en passer pendant aussi longtemps.

Expert SQL Server - Scripter la création d'une base données - Powershell SQL Server  - mgc

Suivant le niveau de mise à jour de votre instance moteur SQL Server 2016, vous pouvez être confronté à l’erreur ci-dessous. Il suffit d’appliquer le dernier CU.

Database cloning for ‘AdventureWorks2016CTP3’ has started with target as ‘AdventureWorks2016CTP3Clone’.
Msg 596, Level 21, State 1, Line 0
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

Expert SQL Server - Scripter la création d'une base données - Powershell SQL Server  - error_dbcc_clonedb-

SQL Mangament Studio permet déjà de générer un script pour la création des objets d’une base de données (tables, procédures stockées, contraintes, triggers, etc) mais avec DBCC CLONEDATABASE, cela tient finalement à quelques lignes de commande T-SQL.

SMO via Powershell (ou C#)

Contrairement à SQL Mangament Studio, on ne le dira jamais assez, l’approche par script est toujours plus maîtrisable et industrialisable. Pour se faire, on peut utiliser l’assembly SMO pour scripter la création des objets d’une base de données. Contrairement à l’instruction DBCC CLONEDATABASE, cela n’inclus pas les fichiers de la base de données. SQL Server Management Objects (SMO) est une librairie permettant de scripter les objets Microsoft SQL Server, Grégory Boge a fait une présentation assez complète sur le sujet disponible sur youtube. Dans le script Powershell ci-dessous, il y a quelques paramètres à renseigner :

$Instance : Instance concernée
$Database : Base de données concernée
$Username : Login pour authentification SQL (optionnel)
$Password : Password pour authentification SQL (optionnel)
$ScriptPath : Chemin du script généré (par défaut : dossier où est stocké le script Powershell)

Au besoin on peut exclure la création de type d’objet dans la génération du script. Il suffit de le spécifier dans les options de génération : $CreationScriptOptions

Pour générer un script de création et de suppression des index via l’assembly SMO :

https://www.concatskills.com/2017/04/03/scripter-creation-et-suppression-des-index/

Pour générer un script de création et de suppression des contraintes via l’assembly SMO :

https://www.concatskills.com/2017/04/19/scripter-creation-suppression-contraintes-fk/

Auteur

Expert SQL Server - Scripter la création d'une base données - Powershell SQL Server  - avatar_ninja_tete-150x150
Sarah Béquet
Archietcte Data Microsoft, les maîtres mots sont : performance, industrialisation, méthodologie & bonne humeur.
error: