Backup όλων των αντικειμένων μιάς βάσης ενός Microsoft Sql Server (ddl)

Στο άρθρο αυτό θα μιλήσουμε για το πως μπορεί ένας χρήστης να κάνει 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]