PoshCode Logo PowerShell Code Repository

Backup-DatabaseObject by Chad Miller 29 months ago
embed code: <script type="text/javascript" src="http://PoshCode.org/embed/3367"></script>download | new post

The Backup-DatabaseObject function backs up a database object definition by scripting out the object to a .sql text file.

  1. add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
  2. add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
  3. add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
  4. add-type -AssemblyName "Microsoft.SqlServer.SqlEnum, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
  5. add-type -AssemblyName "Microsoft.SqlServer.Management.Sdk.Sfc, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
  6.  
  7.  
  8. #######################
  9. <#
  10. .SYNOPSIS
  11. Backs up a database object definition.
  12. .DESCRIPTION
  13. The Backup-DatabaseObject function backs up a database object definition by scripting out the object to a .sql text file.
  14. .EXAMPLE
  15. Backup-DatabaseObject -ServerInstance Z002 -Database AdventureWorks -Schema HumanResources -Name vEmployee -Path "C:\Users\Public"
  16. This command backups up the vEmployee view to a .sql file.
  17. .NOTES
  18. Version History
  19. v1.0   - Chad Miller - Initial release
  20. #>
  21. function Backup-DatabaseObject
  22. {
  23.     [CmdletBinding()]
  24.     param(
  25.     [Parameter(Mandatory=$true)]
  26.     [ValidateNotNullorEmpty()]
  27.     [string]$ServerInstance,
  28.     [Parameter(Mandatory=$true)]
  29.     [ValidateNotNullorEmpty()]
  30.     [string]$Database,
  31.     [Parameter(Mandatory=$true)]
  32.     [ValidateNotNullorEmpty()]
  33.     [string]$Schema,
  34.     #Database Object Name
  35.     [Parameter(Mandatory=$true)]
  36.     [ValidateNotNullorEmpty()]
  37.     [string]$Name,
  38.     [Parameter(Mandatory=$true)]
  39.     [ValidateNotNullorEmpty()]
  40.     [string]$Path
  41.     )
  42.    
  43.     $server = new-object Microsoft.SqlServer.Management.Smo.Server($ServerInstance)
  44.     $db = $server.Databases[$Database]
  45.  
  46.     #Create a UrnCollection. URNs are used by SMO as unique identifiers of objects. You can think of URN like primary keys
  47.     #The URN format is similar to XPath
  48.     $urns = new-object Microsoft.SqlServer.Management.Smo.UrnCollection
  49.  
  50.     #Get a list of database object which match the schema and object name specified
  51.     #New up an URN object and add the URN to the urns collection
  52.     $db.enumobjects() | where {$_.schema -eq $Schema -and  $_.name -eq $Name } |
  53.         foreach {$urn = new-object Microsoft.SqlServer.Management.Sdk.Sfc.Urn($_.Urn);
  54.                  $urns.Add($urn) }
  55.  
  56.     if ($urns.Count -gt 0) {
  57.        
  58.         #Create a scripter object with a connection to the server object created above
  59.         $scripter = new-object Microsoft.SqlServer.Management.Smo.Scripter($server)
  60.        
  61.         #Set some scripting option properties
  62.         $scripter.options.ScriptBatchTerminator = $true
  63.         $scripter.options.FileName = "$Path\BEFORE_$Schema.$Name.sql"
  64.         $scripter.options.ToFileOnly = $true
  65.         $scripter.options.Permissions = $true
  66.         $scripter.options.DriAll = $true
  67.         $scripter.options.Triggers = $true
  68.         $scripter.options.Indexes = $true
  69.         $scripter.Options.IncludeHeaders = $true
  70.        
  71.         #Script the collection of URNs
  72.         $scripter.Script($urns)
  73.        
  74.     }
  75.     else {
  76.         write-warning "Object $Schema.$Name Not Found!"
  77.     }
  78.  
  79. } #Backup-DatabaseObject

Submit a correction or amendment below (
click here to make a fresh posting)
After submitting an amendment, you'll be able to view the differences between the old and new posts easily.

Syntax highlighting:


Remember me