Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Chad Miller

Chad Miller is a Senior Manager of Database Administration at Raymond James Financial. Chad has worked with Microsoft SQL Server since 1999 and has been automating administration tasks using Windows Powershell since 2007. Chad is the Project Coordinator/Developer of the Powershell-based Codeplex project SQL Server PowerShell Extensions (SQLPSX). Chad leads the Tampa Powershell User Group and is a frequent speaker at users groups, SQL Saturdays and Code Camps.

Backup Database Object

I saw this question in one of forums on backing up i.e. scripting out a database object. The problem is easy to solve, but only if you’re familiar with SMO :). Even so, there some more obscure aspects of SMO like URNs which not many people are aware of. If you read the MSDN docs on SMO you’ll find URNs are referenced in a few places. I haven’t used them much, but for this case ¬†it makes sense. Normally if you want to get to an object in SMO you’d reference the server, then the database then the object type collection (StoredProcedures, Views, etc.), and then the object; ¬†however if you don’t know the object type you can call EnumObject method on the database to get a list of objects with its URN. The URN is like a primary key of objects in SMO. So, here’s my solution with with comments…

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
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:UsersPublic"
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 = "$PathBEFORE_$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

And here’s example of sourcing and calling the function:

1
2
. ./Backup-DatabaseObject.ps1
Backup-DatabaseObject -ServerInstance Z002 -Database AdventureWorks -Schema HumanResources -Name vEmployee -Path "C:UsersPublic"

I’ve posted the code on PoshCode also.

Comments

Leave a comment on the original post [sev17.com, opens in a new window]

Loading comments...