Tworzenie backup'u za pomocą scripter'a w PS

Ostatnio musiałem tworzyć skrypty do tworzenia bazy danych (od zera) w sql server. Kod zamieszczony jest na github'ie i projekt nazywa się BackupSqlServerPS. Projekt nazwałem backupem bo oprócz generowania t-sql skryptów do bazy danych to są tworzone skrypty do sql server jobów, zapisywane są logi z bazy i jobów, zapisywane są dane z tabel w formacie csv oraz tworzone są skrypty z danymi w zapytaniami "insert into values". Projekt jest w budowie. Jeszcze chciałbym dodać export użytkowników, execution planów, diagramów i assemblies oraz tworzenie bak'ów. Jeszcze dużo przede mną :)

Najważniejszą funkcją do backupów jest tworzenie scryptera. Z mechanizmu skryptera pewnie już korzystałeś

Za każdym razem jak w Sql Server Management Studio przez menu kontekstowe tworzysz zapytanie 'select ...', create, drop lub podobne to, to zapytanie jest tworzone przez scryptera.

Tak na prawdę skrypter to obiekt klasy Microsoft.SqlServer.Management.SMO.Scripter z pakietu Sqlserver Management Objects. Najwięcej problemów było przy ustawianiu opcji scriptera. Poniżej zamieściłem, krótki przykład tworzenia skryptów do tworzenia tabel w bazie danych. Oczywiście napisałem to w PS.
Na początku musisz załadować SMO:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") 

Tworzymy obiekt reprezentujący server:

$srvConn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
$srvConn.ServerInstance = 'localhost'
$srvConn.Login = 'user'
$srvConn.Password = 'passwd'
$srv = New-Object Microsoft.SqlServer.Management.SMO.Server($srvConn)

Tworzymy obiekt grający pierwsze skrzypce:

$scripter   = New-Object Microsoft.SqlServer.Management.SMO.Scripter($srv)

$scripter.Options.IncludeDatabaseContext = $false
$scripter.Options.WithDependencies       = $false 
$scripter.Options.AllowSystemObjects     = $false
$scripter.Options.NoCommandTerminator    = $false
$scripter.Options.ScriptSchema           = $true 
$scripter.Options.SchemaQualify          = $true
$scripter.Options.DriAll                 = $true

Teraz wyciągamy wszystkie obiekty z bazy danych

$db = $srv.Databases['database']
$tbl = $db.tables | ? {-not $_.IsSystemObject }
foreach ($o in $tbl) {  
{
   $scripter.Options.FileName = $o.Name + ".sql"
   $scripter.Script($o) 
}

Jeżeli będziemy tworzyć inserty z danymi to zamiast $scripter.Script($o) powinniśmy wywołać $scripter.EnumScript($o)

Skrypt główny, który robi wszystko ('śpiewa i tańczy') nazywa się backup-sqlserver.ps1

Oprócz tabel to tworzymy skrypty do Stored Procedures, widoków, user-defined functions oraz typów.
Po uruchomieniu skryptu będziemy mieli foldery w którym będą skrypty do tworzenia obiektów.