Automatisiertes Sichern von Datenbanken bzw. deren Skripte ist eine oftmals notwendige Tätigkeit eines DBA.

Export Schema mittels SQL Server Management Studio

SSMS bietet daher out-of-the-Box die Möglichkeit das automatisiert zu machen:

genaue Anleitungen siehe:

Automatisierung?

Das mag zwar sinnvoll sein, wenn man mal schnell von einer DB manuell eine “Sicherung” ziehen möchte, um sie beispielsweise auf GitHub zu veröffentlichen. Um jedoch mehrere Datenbanken zu sichern oder automatisiert die Datenbanken rauszuschreiben, ist es absolut nicht geeignet. Welche anderen Methoden gibt es:

Methode 1: powershell

Hinsichtlich der Powershell-Umgebung muss man 3 Ansätze differenzieren

  • (a) SMO – Methode 1.1, 1.2, 1.3
  • (b) Microsoft SQL Server Modul sqlps / ab 2016 SQLServer Modul – Methode 1.4
  • (c) das GitHub Projekt sqlpsx – Methode 4

Die in meinen Augen beste Methode ist die Verwendung von SMO (Server Management Objects) unter powershell. (Das geht auch mit .NET) Bei der Verwendung von Powershell und SMo gibt es jedoch auch mehrere Methoden:

Methode 1.1. Microsoft.SqlServer.Management.Smo.ScriptingOptions

Das Grundprinzip besteht darin, die Objekte, die gescripted werden sollen zu ermitteln und diese dann mittels … rauszuschreiben

$so = new-object (‘Microsoft.SqlServer.Management.Smo.ScriptingOptions’)
$objs.Script($so)+”GO” | out-File $OutFile

Die Methode entspricht dem “Generate Script”:

Das von mir meist verwendete Script sieht folgendermaßen aus:

Methode 1.2 Microsoft.SqlServer.Management.Smo.Transfer

Diese Methode ist eigentlich gedacht, dass man Objekte von einem auf einen anderen Server transferiert. Der Funktionsumfang entspricht der SSIS Komponente für Transfer Database Task.

Quelle

Das Prinzip sieht folgendermaßen aus:

$transfer = new-object (“Microsoft.SqlServer.Management.Smo.Transfer”) $db
$transfer.Options.ScriptBatchTerminator = $true # this only goes to the file
$transfer.Options.ToFileOnly = $true # this only goes to the file
$transfer.Options.Filename = “XXX.sql”;
$transfer.ScriptTransfer()

Methode 1.3 Microsoft.SqlServer.Management.Smo.Scripter

Die Scripter Methode entspricht ungefähr dem, das unter “Script XX as” im Kontextmenü zur Verfügung steht:

Der Aufbau sieht folgendermaßen aus:

$scripter = New-Object (‘Microsoft.SqlServer.Management.Smo.Scripter’) ($server)
$scripter.Options.ToFileOnly = $true
$scripter.Options.FileName =”XXX.sql”;
$scripter.Script($object) // $object = ein einzelnes Objekt, dh. es muss innerhalb einer Schleife verwendet werden.

Methode 1.4 Powershell SQL Modul & .EnumScript() bzw .Script()

weiterführende Links

Methode 2: MSSQL-Scripter utility

Beim mssql-scripter handelt es sich um ein python script, das die Funktionalität des “Generate Scripts Wizard” nachbaut. Da ich ihn noch nie verwendet habe, kann ich dazu nichts sagen.

Methode 3: sqlpubwiz (Microsoft SQL Server Publishing Wizard file) – Tool zu SSMS

Bis SQL 2008 wurde sqlpubwiz separat ausgeliefert und war unter (C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\sqlpubwiz) zu finden. Nun ist es in SSMS.exe integriert und verwendet die Microsoft.SqlServer.Management.SqlScriptPublishUI.dll.

Methode 4: Get-SQLScripter

In Kombination mit dem SQL Powershell-Modul SQLPSX, kann man Tabellen folgendermaßen exportieren:

Get-SqlDatabase -dbname database1 -sqlserver server | Get-SqlTable | Get-SqlScripter | Set-Content -Path D:\scripts\script.sql
Get-SqlDatabase -dbname database1 -sqlserver server | Get-SqlStoredProcedure | Get-SqlScripter
Get-SqlDatabase -dbname database1 -sqlserver server | Get-SqlView | Get-SqlScripter

Seit es die Methode 1.4 gibt, gibt es keinen wirklichen Vorteil durch dieses SQL Modul in Bezug auf das Herausschreiben von Skripten.

Methode 5: sp_OACreate 

Neben diesen Methoden gibt es noch die Methode direkt im SQL, die jedoch in den meisten Fällen aus Sicherheitsgründe deaktiviert ist. SQL Server blocked access to procedure ‘sys.sp_OACreate’ of component ‘Ole Automation Procedures’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ole Automation Procedures’ by using sp_configure. For more information about enabling ‘Ole Automation Procedures’, search for ‘Ole Automation Procedures’ in SQL Server Books Online.

Nayan Patel – generate script for all stored procedures for the given database.

Methode 6: eigenständige Programme (kostenpflichtig)

Fazit

Es gibt doch einige Methoden, die man verwenden kann, um Skripte für SQL-Objekte zu erzeugen. Welche ist aber nun die beste?
Wenn man mal schnell eine Tabelle oder so exportieren möchte, empfiehlt sich Methode 1.4 (.Script) . In den anderen Fällen verwende ich das Script unter 1.1 Microsoft.SqlServer.Management.Smo.ScriptingOptions.