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.

{ 4 comments… add one }

  • Garry Bargsley April 17, 2012 at 9:24 pm

    I think this is in response to my question I posted on a couple powershell forums. I am so glad it intrigued you enough to figure it out. I sure would not have gotten all these pieces together to get it to work. I can’t wait until the morning when I get back to the office to try it out.

    Thank you, Thank you.
    Garry

    Reply edit
    • Chad Miller April 17, 2012 at 10:18 pm

      You’re welcome. Although there’s good coverage in Powershell with builtin cmdlets, SQL Server management is a bit tricky and requires much more programming/scripting. It can difficult just to figure out where to start. If you have problems with the script–let me know. I am loading some assemblies only available on 2008 and higher, but the server you connect to can be any version. If needed I adjust the assembly loading to work with SQL 2005 also.

      Reply edit
  • Garry Bargsley April 18, 2012 at 9:41 am

    That is awesome, just finished testing.

    Is there a way to load the function automatically when I open PowerShell? Then I can just type the command with parameters and I am good to go.

    Again, thanks so much for this working example…

    Reply edit
    • Chad Miller April 18, 2012 at 1:57 pm

      Sure you can use Powershell profiles.

      If you add sourcing the function to your profile, the function will be available whenever you start powershell and if you place the Backup-DatabaseObject.ps1 file in the same directory as your profile you can add this one line to your profile.ps1:
      . $(Join-Path (split-path $profile) Backup-DatabaseObject.ps1)
      Keep in mind the more things you add to your profile the slower powershell will start.

      Reply edit

Leave a Comment


− 1 = six

%d bloggers like this: