Export de la structure d’une base de donnes SQL Server

Pourquoi un billet sur ce qui est à priori une problématique simple dans SQL Server ?

Oui, pourquoi ne pas simplement utiliser l’assistant de SSMS qui permet de scripter une base de données, la structure de tous les objets ?

Tout simplement parce qu’il est cas où l’on veut planifier de manière régulière cette opération. Il est donc hors de question que cela soit une opération manuelle dans SQL Server Management Studio.

Pourquoi ne pas simplement faire un backup de la base de donnes ?
Lorsque la taille des bases de données devient importante (500GB, 1TB) on se trouve confrontés à des problématiques d’espace sur les volumes de backup, de rétention, le temps de sauvegarde et de restauration. Certaines bases voient 95% du contenu être rafraichi tous les jours !

C’est là ma problématique. Une base de type DataWharehouse de volumétrie vraiment conséquente et donc les données sont réintégrées en totalité toues les nuits. Le backup ne me “sert” finalement pas à grand chose. Car une fois la restore effectuée, on relance les scripts d’importation de données depuis divers environnements.

Au final il est apparu que seule la structure de la base était utile, d’un point de vue sauvegarde. D’où la question : comment exporter la structure d’une base ?

Alors, oui, en puriste, je devrais dire qu’il y a un gestionnaire de code source avec son versionning qui détient LA bonne version. mais dans la vraie vie ….

S’offre alors à nous plusieurs solutions …

J’avais par le passé travaillé avec du PowerShell et l’objet Scripter de SMO.

 $Scripter.Options.DriAll=$False ;
 $Scripter.Options.IncludeHeaders=$False ;
 $Scripter.Options.ToFileOnly=$True ;
 $Scripter.Options.WithDependencies=$True  ;
 $Scripter.Options.FileName=$Sqlfile ;
 $Scripter.Options.ScriptDrops=$False ; 
 $Scripter.Options.IncludeIfNotExists=$False ; 
 $Scripter.Options.AppendToFile=$True ;

Il suffisait ensuite de boucler ave du ForEach sur les différents objets pour obtenir le résultat escompté.

On peut aussi utiliser le Data-tier Application Framework (DACFx). Il suffit alors de créer un DacPac (attention, pas le BacPac, qui, lui contient aussi les données … ). Encore une fois, cette opération est manuelle dans SSMS. Mais avec un peu d’imagination on peut tout a fait programmer tout cela via du PowerShell et l’ordonnancer avec un Agent SQL ou autre.
J’avoue que je ne suis pas vraiment fan de cette solution (tout n’est pas supporté d’un point de vue de la structure) même si au travers des modules fournis dans l’incontournable pack @psdbatools (http://dbatools.io) il est simple d’appeler la commande Export-DbaDacpac pour ensuite l’automatiser.

Export-DbaDacpac -sqlinstance MyInstance -Database MyDatabase  -Path C:\temp 

Une dernière possibilité s’offre à nous … très simple à mettre en œuvre!

Tout d’abord il suffit de faire appel à la commande DBCC CLONEDATABASE qui permet de créer un clone de la base de données … sans les données. On obtient donc la structure. A nous de juger si les valeurs de statistiques d’index sont pertinentes ou pas. J’avais déjà présenté cette fonctionnalité qui est a présent disponible dans toutes les versions de SQL Server à compter de SQL Server 2012 SP4.

DBCC CLONEDATABASE (MyDatabase, MyClone) WITH NO_STATISTICS

ou bien, au travers des dbaTools :

Invoke-DbaDatabaseClone -SqlInstance MyInstance -Database MyDatabase -CloneDatabase MyClone 

Une fois le clone terminé, il ne reste plus qu’à sauvegarder cette base comme une base lambda.

Et le tour est joué. Certes, ce n’est pas du script de base à proprement parler, mais pour qui veut archiver la structure d’une base sans ses données, la solution reste élégante. On peut ensuite restaurer cette base sur n’importe quel serveur et en extraire les scripts au besoin.

Happy scripting