Στο άρθρο αυτό θα μιλήσουμε για το πως μπορεί ένας χρήστης να κάνει export το schema μιας βάσης ενός microsoft sql server με την βοήθεια powershell scripting. Πολλές φορές θέλουμε να παίρνουμε το schema μιας βάσης για λόγους versioning και θα θέλαμε αυτό να γίνεται τακτικά και αυτόματα με καποίο job ή script. Λοιπόν αυτό μπορεί να φαίνεται και να είναι εύκολο για μια βάση Oracle (χρήση export εργαλείου) αλλά για έναν Microsoft Sql Server τα πράγματα δεν είναι τόσο απλά.
Για να μπορέσει κάποιος να φτιάξει μια αυτόματη διαδιακασία που θα κάνει export το schema της βάσης καθημερινά θα πρέπει εν ολίγης να κάνει reverse engineering στο εργαλείο που χρησιμοποιεί το management studio. Έφαγα πολλές ανθρωποώρες για να καταλήξω στην δημιουργία κατάλληλου powershell script που να μπορεί να κάνει χρήση του Microsoft smo scripter και να παράγει σε διάφορα αρχεία .sql το σχήμα της βάσης. Για να είμαι σίγουρος ότι προχωράω σωστά χρησιμοποίησα ένα πολύ χρήσιμο εργαλείο που σύγκρινε το σχήμα του script με το σχήμα της βάσης από όπου το πήρα (το σχήμα που εβγαλά το έριχνα σε μια τεστ βάση για να κάνω την σύγκριση). Το εργαλείο είναι το SQL Examiner και εικόνα του ακολουθεί.
Τελικά μετά απο πολύ δουλεία και ψάξιμο κατέληξα στο παρακάτω Powershell script το οποίο το κοινοποιώ σε όποιο θέλει να κάνει κάτι αντίστοιχο. Ελπίζω να σώσω πολλές ανθρωποώρες δουλειάς απο κάποιον και να σας φανεί χρήσιμο. Χρήσιμες συμβουλές είναι ότι αν θέλει καποιός να φέρει σε ένα DR περιβάλλον όλη την βάση θα πρέπει να τρέξει τα scripts με την εξής σειρά:
1. users, 2. Roles, 3. Schemas, 4. Database Triggers, 5. User Defined Data Types, 6. Defaults, 7. Tables, 8. Tables Trigger, 9. Functions, 10. Views, 11. Store Procedures, 12. Rules, 13. Synonyms, 14. Xml Schema, 15. CLR assemblies.
Παρακάτω ακολουθεί το powershell script που κάνει export ολο το schema. To Script αυτό μπορεί να τρέχει σε έτσι όπωσ φαίνεται στην πρώτη γραμμή και να οριστεί ένα schedule task που να το καλεί όταν κάποιος θέλει αν το καλέσει:
# Usage: powershell ExportSchema.ps1 "SERVERNAME" "DATABASE" "C:\<YourOutputPath>"
# Start Script
Set-ExecutionPolicy RemoteSigned
# Set-ExecutionPolicy -ExecutionPolicy:Unrestricted -Scope:LocalMachine
function GenerateDBScript([string]$serverName, [string]$dbname, [string]$scriptpath)
{
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("System.Data") | Out-Null
$srv = new-object "Microsoft.SqlServer.Management.SMO.Server" $serverName
$srv.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.View], "IsSystemObject")
$db = New-Object "Microsoft.SqlServer.Management.SMO.Database"
$db = $srv.Databases[$dbname]
$scr = New-Object "Microsoft.SqlServer.Management.Smo.Scripter"
$deptype = New-Object "Microsoft.SqlServer.Management.Smo.DependencyType"
$scr.Server = $srv
$options = New-Object "Microsoft.SqlServer.Management.SMO.ScriptingOptions"
$options.AllowSystemObjects = $true
$options.IncludeDatabaseContext = $true
$options.IncludeIfNotExists = $true
$options.ClusteredIndexes = $true
$options.Default = $true
$options.DriAll = $true
$options.Indexes = $true
$options.Triggers = $true
$options.NonClusteredIndexes = $true
$options.IncludeHeaders = $false
$options.ToFileOnly = $true
$options.AppendToFile = $true
$options.ScriptDrops = $false
$options.Permissions = $true
$options.WithDependencies = $true
$options.ExtendedProperties =$true
#$options.Logins=$true
#$scr."Object-Level Permissions" = $true
#$scr.Owner = $true
$db_role = New-Object "Microsoft.SqlServer.Management.SMO.DatabaseRole"
$db2 = $srv.Databases.Item($dbname)
# Set options for SMO.Scripter
$scr.Options = $options
#pairnw tin hmeromhnia kai thn vazw sto arxeio
$a = Get-Date
$day=$a.Day
$month=$a.Month
$year=$a.Year
#=============
# ASSEMBLY
#=============
$DBAssemblies = $db.assemblies # where {$_.IsSystemObject -eq $false}
$options.FileName = $scriptpath + "\$($dbname)_db_assemblies.sql"
New-Item $options.FileName -type file -force | Out-Null
Foreach ($db_assembly in $DBAssemblies )
{
$DBAssemblies | foreach {$_.Script()+ "GO"} | Out-File $($options.FileName)
}
#=============
# Synonyms
#=============
$DBSynonyms = $db.Synonyms # where {$_.IsSystemObject -eq $false}
$options.FileName = $scriptpath + "\$($dbname)_db_rules.sql"
New-Item $options.FileName -type file -force | Out-Null
Foreach ($db_synonym in $DBSynonyms )
{
$DBSynonyms | foreach {$_.Script()+ "GO"} | Out-File $($options.FileName)
}
#=============
# Table Triggers
#=============
$options.FileName = $scriptpath + "\$($dbname)_table_triggers.sql"
New-Item $options.FileName -type file -force | Out-Null
Foreach ($tb in $db.Tables)
{
if($tb.triggers -ne $null)
{
foreach ($trigger in $tb.triggers)
{
$tb.triggers| foreach {$_.Script()+ "GO"} | Out-File -Append $($options.FileName)
#$scr.Script($trigger)
}
}
}
#=============
# Users
#=============
$DBUsers = $db.Users # where {$_.IsSystemObject -eq $false}
$options.FileName = $scriptpath + "\$($dbname)_db_users.sql"
New-Item $options.FileName -type file -force | Out-Null
Foreach ($db_users in $DBUsers)
{
$DBUsers | foreach {$_.Script()+ "GO"} | Out-File $($options.FileName)
}
#=============
# Rules
#=============
$DBRules = $db.Rules # where {$_.IsSystemObject -eq $false}
$options.FileName = $scriptpath + "\$($dbname)_db_rules.sql"
New-Item $options.FileName -type file -force | Out-Null
Foreach ($db_rule in $DBRules )
{
$DBRules | foreach {$_.Script()+ "GO"} | Out-File $($options.FileName)
}
#=============
# DEfaults
#=============
$DBDefaults = $db.Defaults # where {$_.IsSystemObject -eq $false}
$options.FileName = $scriptpath + "\$($dbname)_db_defaults.sql"
New-Item $options.FileName -type file -force | Out-Null
Foreach ($db_default in $DBDefaults)
{
$DBDefaults | foreach {$_.Script()+ "GO"} | Out-File $($options.FileName)
}
#=============
# UserDefinedDataType
#=============
$DBtypes = $db.UserDefinedDataTypes # where {$_.IsSystemObject -eq $false}
$options.FileName = $scriptpath + "\$($dbname)_db_types.sql"
New-Item $options.FileName -type file -force | Out-Null
Foreach ($db_type in $DBtypes)
{
$DBtypes| foreach {$_.Script()+ "GO"} | Out-File $($options.FileName)
}
#=============
# DB Schemas
#=============
$DBSchemas = $db.schemas # where {$_.IsSystemObject -eq $false}
$options.FileName = $scriptpath + "\$($dbname)_db_schemas.sql"
New-Item $options.FileName -type file -force | Out-Null
Foreach ($db_schemas in $DBSchemas)
{
$DBSchemas| foreach {$_.Script()+ "GO"} | Out-File $($options.FileName)
}
#=============
# DB Roles
#=============
$DBRoles = $db.Roles # where {$_.IsSystemObject -eq $false}
$options.FileName = $scriptpath + "\$($dbname)_db_roles.sql"
New-Item $options.FileName -type file -force | Out-Null
Foreach ($db_role in $DBRoles)
{
$DBRoles| foreach {$_.Script()+ "GO"} | Out-File $($options.FileName)
}
#=============
# Tables
#=============
$options.FileName = $scriptpath + "\$($dbname)_tables.sql"
New-Item $options.FileName -type file -force | Out-Null
Foreach ($tb in $db.Tables)
{
If ($tb.IsSystemObject -eq $FALSE)
{
$smoObjects = New-Object Microsoft.SqlServer.Management.Smo.UrnCollection
$smoObjects.Add($tb.Urn)
$scr.Script($smoObjects)
}
}
#=============
# Views
#=============
$options.FileName = $scriptpath + "\$($dbname)_views.sql"
New-Item $options.FileName -type file -force | Out-Null
$views = $db.Views | where {$_.IsSystemObject -eq $false}
Foreach ($view in $views)
{
if ($views -ne $null)
{
$scr.Script($view)
}
}
#=============
# StoredProcedures
#=============
$StoredProcedures = $db.StoredProcedures | where {$_.IsSystemObject -eq $false}
$options.FileName = $scriptpath + "\$($dbname)_stored_procs.sql"
New-Item $options.FileName -type file -force | Out-Null
Foreach ($StoredProcedure in $StoredProcedures)
{
if ($StoredProcedures -ne $null)
{
$scr.Script($StoredProcedure)
}
}
#=============
# Functions
#=============
$UserDefinedFunctions = $db.UserDefinedFunctions | where {$_.IsSystemObject -eq $false}
$options.FileName = $scriptpath + "\$($dbname)_functions.sql"
New-Item $options.FileName -type file -force | Out-Null
Foreach ($function in $UserDefinedFunctions)
{
if ($UserDefinedFunctions -ne $null)
{
$scr.Script($function)
}
}
#=============
# DBTriggers
#=============
$DBTriggers = $db.Triggers #| where {$_.IsSystemObject -eq $false}
$options.FileName = $scriptpath + "\$($dbname)_db_triggers.sql"
New-Item $options.FileName -type file -force | Out-Null
foreach ($trigger in $DBTriggers)
{
if ($DBTriggers -ne $null)
{
#$scr.Script($DBTriggers)
$DBTriggers| foreach {$_.Script()+ "GO"} | Out-File $($options.FileName)
}
}
}
#=============
# Execute
#=============
$scrap = "-+- Exporting by exportschema.ps1 for " + $args[0] + " " + $args[0] + " " + $args[1] + " " + $args[2]
Write-Output $scrap
GenerateDBScript $args[0] $args[1] $args[2]
