Import/Export des schémas d’une BD SQL Server

Après plusieurs recherche chez Microsoft pour réaliser cette manipulation, je n’ai rien trouvé de vraiment concret, juste les liens suivant pour SQL 7 & 2000 et SQL 2005.

Ce que j’apprend c’est  qu’anciennement les diagrammes étaient sauvés dans la table dtproperties et maintenant sous SQL 2005 dans la table sysdiagrams.

En fait la meilleure solution que j’ai trouvé ce trouve sur le site de The Code Project. L’article est en anglais, mais je vais faire un tuto rapide, il n’y a rien de compliquer.

1. Downloader le fichier Zip qui comporte 2 fichiers, à savoir :
   - Tool_ScriptDiagram2005.sql
   - Tool_VarbinaryToVarcharHex.sql
   
   Tool_ScriptDiagram2005.sql contient la procédure stocké qui vas extraire les informations de la table sysdiagrams et lui appliquer un template, afin de rendre autonome le code sql généré pour importer le schéma dans une autre bd ultérieurement.
   
   Tool_VarbinaryToVarcharHex.sql est une fonction qui est appelé par la procédure stocké.
   Elle transforme le binaire du schéma en une liste de valeur hexadecimal exploitatble dans un ficher sql.
   
   
2. Ouvrir SQL Server et intégrer les 2 scripts sql dans la BD qui vous intéresse.
   
   
3. Pour extraire un schéma. Demander une nouvelle requête afin de saisir et exécuter la commande suivante :
   exec Tool_ScriptDiagram2005 'LeNomDeMonSchema'
   Sauvegarder le résultat généré dans un fichier, par exemple monSchéma.sql.
   
   
4. Pour importer un diagramme, rien de plus simple ouvrir le fichier sql du schéma généré et l'exécuter.
   La procédure stocké et la fonction "Tool_..." ne sont pas nécessaire. Le schéma sera recréé avec le même nom suivi de la date et l'heure d'import pour ne pas écraser le schéma existant.

Dans mon cas, je restaure souvent ma BD et je repars à vide donc je préfère que la date et l'heure ne soit pas présent derrière le nom du diagramme.
Pour cela j'ai modifié la procédure stocké existante "Tool_ScriptDiagram2005" en ajoutant les lignes de code suivantes:

  PRINT '    PRINT ''Removed uniqueness when diagram ' + @name + ' not exist'''
  SELECT @line =  
        '    IF not exists (SELECT [name] FROM sysdiagrams WHERE [name] = '''+ @name +''') '
  PRINT @line
  PRINT '        SET @DiagramSuffix = '''''

à insérer juste avant la ligne existante :

  PRINT '    PRINT ''Write diagram ' + @name + ' into new row (and get [diagram_id])'''

Tout ça, c'est bien, mais généralement on a plein de diagramme à gérer, donc le faire manuellement ce n'est pas le top. Il est possible d'utiliser des fichiers batch pour aller plus vite.

Pour extraire des schémas (Extract.bat):

@set SERVER=(local)
@set DB=NomDeMaDatabase

sqlcmd -d %DB% -E -S %SERVER% ^
 -Q "exec Tool_ScriptDiagram2005 'NomDeMonSchema'" ^
 -o NomDeMonSchema.sql

Pour Importer des schémas (Import.bat):

@set SERVER=(local)
@set DB=NomDeMaDatabase

:VERIF_ARGS
@REM Si arguments non vide on les prends en compte
@IF NOT "%~1"==""  @set SERVER=%~1
@IF NOT "%~2"==""  @set DB=%~2

:IMPORT
sqlcmd -d %DB% -E -S %SERVER% ^
 -i NomDeMonSchema1.sql ^
 -i NomDeMonSchema2.sql ^
 -i NomDeMonSchema3.sql ^
 -o OUTPUT_DIAGRAMS_IMPORT.log 

La section :VERIF_ARGS est utile si on a un autre batch qui l'appel. L'idée est d'être capable de l'appliquer pour différents environnement, comme Test ou Préproduction par exemple, par défaut tout est local.

L'option "-o OUTPUT_DIAGRAMS_IMPORT.log" permet d'avoir une log de l'exécution des imports afin de traquer les erreurs.

Voici un exemple du batch de restauration de ma bd en local qui appel l’import et met en place les 2 SQL pour gérer les diagrammes :

@echo on

@set SERVER=(local) 
@set DB=NomDeMaDatabase

:Restore DB - Version nnn
sqlcmd -d %DB% -E -S %SERVER%  -i Restore.sql -o OUTPUT_RESTORE.txt:CREATE DIAGRAMS
@echo.
@echo ** CREATE DIAGRAMS **
@cd Diagrams
@call import.bat %SERVER% %DB%
@echo.
@echo ** SQL TOOLS FOR DIAGRAMS **
sqlcmd -d %DB% -E -S %SERVER% -i "Tool_ScriptDiagram2005.sql"
sqlcmd -d %DB% -E -S %SERVER% -i "Tool_VarbinaryToVarcharHex.sql"
@cd ..

Tous mes scripts de schémas et tools sont mis dans un répertoire que j’ai nommé Diagrams.

Et un exemple du fichier Restore.sql pour avoir une solution complête :

DECLARE @TARGET_MDF VARCHAR(MAX)
DECLARE @TARGET_LDF VARCHAR(MAX)
DECLARE @SOURCE VARCHAR(MAX)

SET @SOURCE = 'C:\...\NomDuBackupDeMaBD.BAK' 
SET @TARGET_MDF = 'C:\...\MSSQL\Data\NomDeMaBD.mdf'
SET @TARGET_LDF = 'C:\...\MSSQL\Data\NomDeMaBD_Log.ldf'

USE master

PRINT '-- Drop DB --'

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'NomDeMaBD')
    ALTER DATABASE [NomDeMaBD] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'NomDeMaBD')
    DROP DATABASE [NomDeMaBD]


PRINT '-- Restore DB --'

RESTORE DATABASE [NomDeMaBD]
FROM DISK = @SOURCE
WITH 
MOVE 'NomDeMaBD' TO @TARGET_MDF,
MOVE 'NomDeMaBD_log' TO @TARGET_LDF

Voilà, bon usage.

Pour avoir une BD de démo, au besoin, je vous propose de prendre AdvenureWorks ici.