# encoding: ascii
# api: powershell
# title: Backup-DatabaseObject
# description: The Backup-DatabaseObject function backs up a database object definition by scripting out the object to a .sql text file.
# version: 1.0
# type: script
# author: Chad Miller
# license: CC0
# function: Backup-DatabaseObject
# x-poshcode-id: 3367
# x-archived: 2016-01-28T13:42:58
# x-published: 2013-04-17T15:12:00
#
#
add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
add-type -AssemblyName "Microsoft.SqlServer.SqlEnum, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
add-type -AssemblyName "Microsoft.SqlServer.Management.Sdk.Sfc, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
#######################
<#
.SYNOPSIS
Backs up a database object definition.
.DESCRIPTION
The Backup-DatabaseObject function backs up a database object definition by scripting out the object to a .sql text file.
.EXAMPLE
Backup-DatabaseObject -ServerInstance Z002 -Database AdventureWorks -Schema HumanResources -Name vEmployee -Path "C:\Users\Public"
This command backups up the vEmployee view to a .sql file.
.NOTES
Version History
v1.0 - Chad Miller - Initial release
#>
function Backup-DatabaseObject
{
[CmdletBinding()]
param(
[Parameter(Mandatory=$true)]
[ValidateNotNullorEmpty()]
[string]$ServerInstance,
[Parameter(Mandatory=$true)]
[ValidateNotNullorEmpty()]
[string]$Database,
[Parameter(Mandatory=$true)]
[ValidateNotNullorEmpty()]
[string]$Schema,
#Database Object Name
[Parameter(Mandatory=$true)]
[ValidateNotNullorEmpty()]
[string]$Name,
[Parameter(Mandatory=$true)]
[ValidateNotNullorEmpty()]
[string]$Path
)
$server = new-object Microsoft.SqlServer.Management.Smo.Server($ServerInstance)
$db = $server.Databases[$Database]
#Create a UrnCollection. URNs are used by SMO as unique identifiers of objects. You can think of URN like primary keys
#The URN format is similar to XPath
$urns = new-object Microsoft.SqlServer.Management.Smo.UrnCollection
#Get a list of database object which match the schema and object name specified
#New up an URN object and add the URN to the urns collection
$db.enumobjects() | where {$_.schema -eq $Schema -and $_.name -eq $Name } |
foreach {$urn = new-object Microsoft.SqlServer.Management.Sdk.Sfc.Urn($_.Urn);
$urns.Add($urn) }
if ($urns.Count -gt 0) {
#Create a scripter object with a connection to the server object created above
$scripter = new-object Microsoft.SqlServer.Management.Smo.Scripter($server)
#Set some scripting option properties
$scripter.options.ScriptBatchTerminator = $true
$scripter.options.FileName = "$Path\BEFORE_$Schema.$Name.sql"
$scripter.options.ToFileOnly = $true
$scripter.options.Permissions = $true
$scripter.options.DriAll = $true
$scripter.options.Triggers = $true
$scripter.options.Indexes = $true
$scripter.Options.IncludeHeaders = $true
#Script the collection of URNs
$scripter.Script($urns)
}
else {
write-warning "Object $Schema.$Name Not Found!"
}
} #Backup-DatabaseObject