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

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

30 avril 2017

Commentaires fermés

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 !

DBCC CLONEDATABASE ('AdventureWorks2016CTP3', 'AdventureWorks2016CTP3Clone')

USE [master]
GO
ALTER DATABASE [AdventureWorks2016CTP3Clone] SET READ_WRITE WITH NO_WAIT
GO

DBCC CLONEDATABASE ('AdventureWorks2016CTP3', 'AdventureWorks2016CTP3Clone')

USE [master]

ALTER DATABASE [AdventureWorks2016CTP3Clone] SET READ_WRITE WITH NO_WAIT

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.

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.}

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

param(
    [Parameter(Mandatory=$false)] [string]$Instance="localhost",
    [Parameter(Mandatory=$false)] [string]$Database="AdventureWorks2016CTP3",
    [Parameter(Mandatory=$false)] [string]$Username,
    [Parameter(Mandatory=$false)] [string]$Password,
    [Parameter(Mandatory=$false)] [string]$ScriptPath 
    )

clear-host

$ScriptDirectory = Split-Path $MyInvocation.MyCommand.Path

Try
{

    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | out-null
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | out-null
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null 

    if ([string]::IsNullOrEmpty($ScriptPath)) { $ScriptPath = $ScriptDirectory }

    $mySrvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
    $mySrvConn.ServerInstance=$Instance
    if ($Username) {
        $mySrvConn.LoginSecure = $false
        $mySrvConn.Login = $Username
        $mySrvConn.Password = $Password
    }

    $srv=New-Object Microsoft.SqlServer.Management.Smo.Server($mySrvConn)

    $db = $srv.Databases.Item($Database)

    if ($db.name -ne $Database) { Throw "Can't find the database '$Database' in $Instance" }

    $My = "Microsoft.SqlServer.Management.Smo"

    $transfer = new-object ("$My.Transfer") $db

    $CreationScriptOptions = new-object ("$My.ScriptingOptions") 

    $CreationScriptOptions.ContinueScriptingOnError = $true
    $CreationScriptOptions.DriWithNoCheck=$true
    $CreationScriptOptions.ExtendedProperties= $true
    $CreationScriptOptions.DRIAll= $true
    $CreationScriptOptions.Indexes= $true 
    $CreationScriptOptions.Triggers= $true 
    $CreationScriptOptions.FullTextCatalogs = $true
    $CreationScriptOptions.FullTextIndexes = $true
    $CreationScriptOptions.FullTextStopLists = $true
    $CreationScriptOptions.ScriptBatchTerminator = $true
    $CreationScriptOptions.IncludeHeaders = $true
    $CreationScriptOptions.ToFileOnly = $true
    $CreationScriptOptions.IncludeIfNotExists = $false
    $CreationScriptOptions.Filename =  "$ScriptPath\$($db.Name)_Objects.sql";
 
    $transfer.options=$CreationScriptOptions

    $transfer.ScriptTransfer()

}
Catch
{   
  $errorMessage = $_.Exception.Message
  $line = $_.InvocationInfo.ScriptLineNumber
  $script_name = $_.InvocationInfo.ScriptName
  Write-Host "Error: Occurred on line $line in script $script_name." -ForegroundColor Red
  Write-Host "Error: $ErrorMessage" -ForegroundColor Red
}

param(

[Parameter(Mandatory=$false)] [string]$Instance="localhost",

[Parameter(Mandatory=$false)] [string]$Database="AdventureWorks2016CTP3",

[Parameter(Mandatory=$false)] [string]$Username,

[Parameter(Mandatory=$false)] [string]$Password,

[Parameter(Mandatory=$false)] [string]$ScriptPath

)

clear-host

$ScriptDirectory = Split-Path $MyInvocation.MyCommand.Path

Try

{

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | out-null

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | out-null

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

if ([string]::IsNullOrEmpty($ScriptPath)) { $ScriptPath = $ScriptDirectory }

$mySrvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection

$mySrvConn.ServerInstance=$Instance

if ($Username) {

$mySrvConn.LoginSecure = $false

$mySrvConn.Login = $Username

$mySrvConn.Password = $Password

}

$srv=New-Object Microsoft.SqlServer.Management.Smo.Server($mySrvConn)

$db = $srv.Databases.Item($Database)

if ($db.name -ne $Database) { Throw "Can't find the database '$Database' in $Instance" }

$My = "Microsoft.SqlServer.Management.Smo"

$transfer = new-object ("$My.Transfer") $db

$CreationScriptOptions = new-object ("$My.ScriptingOptions")

$CreationScriptOptions.ContinueScriptingOnError = $true

$CreationScriptOptions.DriWithNoCheck=$true

$CreationScriptOptions.ExtendedProperties= $true

$CreationScriptOptions.DRIAll= $true

$CreationScriptOptions.Indexes= $true

$CreationScriptOptions.Triggers= $true

$CreationScriptOptions.FullTextCatalogs = $true

$CreationScriptOptions.FullTextIndexes = $true

$CreationScriptOptions.FullTextStopLists = $true

$CreationScriptOptions.ScriptBatchTerminator = $true

$CreationScriptOptions.IncludeHeaders = $true

$CreationScriptOptions.ToFileOnly = $true

$CreationScriptOptions.IncludeIfNotExists = $false

$CreationScriptOptions.Filename = "$ScriptPath\$($db.Name)_Objects.sql";

$transfer.options=$CreationScriptOptions

$transfer.ScriptTransfer()

}

Catch

{

$errorMessage = $_.Exception.Message

$line = $_.InvocationInfo.ScriptLineNumber

$script_name = $_.InvocationInfo.ScriptName

Write-Host "Error: Occurred on line $line in script $script_name." -ForegroundColor Red

Write-Host "Error: $ErrorMessage" -ForegroundColor Red

}

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

Sarah Béquet

Archietcte Data Microsoft, les maîtres mots sont : performance, industrialisation, méthodologie & bonne humeur.

Derniers articles

2019.12.02BI & Big Data Etude des contextes en DAX (4/4) : Transition de contexte
2019.11.25BI & Big Data Etude des contextes en DAX (3/4) : Contexte de requête
2019.11.17BI & Big Data Etude des contextes en DAX (2/4) : Contexte de filtre
2019.11.11BI & Big Data Etude des contextes en DAX (1/4) : Contexte de ligne

Articles liés

Exécuter un script sur toutes les tables d'une base de données

Configurer Database Mail avec Gmail

Keyser Söze au SQLSaturday

SQL Saturday Paris 2015 - Optimisation de requête

Blog