Powershell i SQL 04 – Skryptowanie obiektów bazy danych

Jeśli miałeś kiedyś do wykonania migrację bazy danych, to wiesz ile przy prostym zadaniu może cię czekać problemów. Wystarczy, że wersja silnika bazy danych w miejscu docelowym jest niższa niż w źródłowym i wszelkie próby skopiowania całej bazy danych są z góry skazane na niepowodzenie. A co jeśli klient zechce zeskryptować tylko niektóre elementy bazy danych? Okazuje się, że powershell i SQL mogą pomóc w tym zadaniu.

Zaczynamy od utworzenia obiektu wskazującego na wybraną instancję SQL. Robimy to tak samo jak w poprzednich przykładach:

$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server localhost

Jedna instrukcja, a już mamy obiekt serwera SQL. Na tym serwerze można się odwołać do baz danych:

$db = $SqlServer.Databases["AdventureWorks2014"]

Za chwilę przyda nam się obiekt bazy danych, który jak widać powyżej odpowiada za bazę AdventureWorks2014. Tymczasem wróćmy do naszego zadania. Mamy stworzyć skrypt tworzący obiekty bazy danych. Do dyspozycji dostajesz klasę Microsoft.SqlServer.Management.Smo.Server. Dokładny opis tej klasy znajdziesz na stronach MSDN. Tak, te strony są dedykowane programistom, ale powershell korzystający z .NET to już nie byle co, tylko prawdziwe programowanie. Jak zwykle, aby otrzymać obiekt tego typu, musisz wywołać konstruktor przekazując do niego nazwę instancji – tutaj localhost:

$scriptor = New-Object Microsoft.SqlServer.Management.Smo.Scripter $SqlServer

Obiekt scriptor ma wiele właściwości, ale dla nas najbadziej ciekawi Options (link do strony z help). Options sam w sobie ma wiele ustawień wpływających na to co i jak będzie skryptowane (pełny opis):

• dodaj do istniejącego już na dysku pliku

$scriptor.Options.AppendToFile = $true

• skryptuj informacje o indeksach clustrowanych

$scriptor.Options.ClusteredIndexes =$true

• skryptuj też polecenie drop przed create

$scriptor.Options.ScriptDrops = $false

• Dodaj sekcję z nagłówkami

$scriptor.Options.IncludeHeaders =$false

• Skryptuj informacje o indeksach tabel

$scriptor.Options.Indexes =$true

• Nic nie wyświetlaj tylko skryptuj do pliku

$scriptor.Options.ToFileOnly =$true

• Skryptuj informacje o relacjach, checkach defaultach – ogólnie constrainach  (DRI = declarative referential integrity )

$scriptor.Options.DriAll = $true

… i wreszcie ścieżka do pliku, gdzie ma być wygenerowany skrypt

$scriptor.Options.FileName = "c:\temp\my_db_script.sql"

Prawie gotowe. Teraz decydujemy co konkretnie ma być zeskryptowane. Załóżmy, że chodzi nam tylko o tabele w bazie danych. W takim przypadku należy stworzyć pętlę, która przejdzie przez wszystkie tabele bazy danych i każdą z nich zeskryptuje zgodnie z wcześniej określonymi parametrami.

foreach($table in $db.Tables)
{
   $scriptor.Script($table)
}