Powershell/SMO: Unload table schema, dri, perms & data.

###############################################################################
# Script_Table_to_File.ps1
# G. Rayburn <g.rayburn AT cox . net>
# 05/02/2008
###############################################################################
# Script single table data ("|" delim) & schema to datestamp directory.
#
# Based on original script by Ben Miller at:
# http://sqlblog.com/blogs/ben_miller/archive/2007/10/15/
# table-scripting-with-smo-part-2-with-powershell.aspx
#
# Depends on:
#             Client having Powershell & SMO installed.
#             Client having access to $server, $dbname, $table & $outroot.
#             Client having bcp installed & permissions to use bcp against $server.
#
# TODO:
#        Enforce parameter submission/ordinal position.
#            Param ([string] $server = $(throw "Servername required.")) works,
#            but produces undesireable output. $args.count appears
#            to be the cleanest output but is harder to validate/enforce
#            ordinal position. 
#
#                "Swing and a miss!" by the PS dev's on parameters, IMO.
#
#        Determine schema other than DBO. By def. it's only looking for
#        & finding DBO schema objects.
#
# Usage:
#
#     .\Script_Table_to_File.ps1 FooServer BarDB 'Foo Table' 'X:\Foo Dir\'
#
###############################################################################
##
## Setup variables and Args checking:
IF ($args.count -ne 4)
    {
        Write-Host
        Write-Host "ERROR: Must pass four args in the form of: Server DBName 'Table' 'OutPath'" 
        Write-Host
        Exit
    }

$DT = Get-Date -UFormat "%Y%m%d"

$server = $args[0] # FooServer
$dbname = $args[1] # BarDB
$table = $args[2] # 'Foo Table' 
$outroot = $args[3] + $DT # 'X:\Foo Dir\'
$outfull = $outroot + "\" + $table + "_TableSchema_" + $DT + ".txt"
$outbcp = $outroot + "\" + $table + "_TableUnload_" + $DT + ".unl"


## Check for existance of $outroot directory: 
IF (!(Test-Path -LiteralPath $outroot))
    {
        New-Item $outroot -Type Directory | Out-Null
    }


## Create a scripting object, specify options and then execute:
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
$srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $server
$db = New-Object ("Microsoft.SqlServer.Management.SMO.Database")
$scr = New-Object ("Microsoft.SqlServer.Management.Smo.Scripter")

    $db = $srv.Databases[$dbname]
    $tb = $db.Tables[$table]
    $scr.Server = $srv

    ## Scripting object options:
    $options = New-Object ("Microsoft.SqlServer.Management.SMO.ScriptingOptions")

        $options.DriAll = $true
        $options.ClusteredIndexes = $true
        $options.Default = $true
        $options.Indexes = $true
        $options.IncludeHeaders = $true
        $options.IncludeDatabaseContext = $true
        $options.Permissions = $true
        $options.AppendToFile = $false
        $options.FileName = $outfull
        $options.ToFileOnly = $true

    $scr.Options = $options

        ## Execute scripting object and report any errors:
        &{
            trap { [System.DateTime]::Now.ToString("T") + " ### ERROR ### $_ Executing scripting object."; break } 
                &{ $scr.Script($tb) };
        }

    Write-Host
    Write-Host "*** SUCCESS *** scripting" $tb "to file."
    Write-Host
    Write-Host
    Write-Host " Beginning unload of" $tb "to file:"
    Write-Host
    Write-Host


## Unload table via bcp:
$bcp = "bcp.exe """ + $db.Name + """.""" + $tb.Schema + """.""" + $tb.Name + """ out """ + $outbcp + """ -q -c -t ""|"" -T -S " + $server

cmd /c $bcp ## cmd /c in PSH enables the $LASTEXITCODE to be returned.

    ## Check error code from cmd.exe:
    IF ($LASTEXITCODE -eq 0)
        {
            Write-Host
            Write-Host
            Write-Host "*** SUCCESS *** unloading table" $tb " to file."
            Write-Host
            Write-Host
            Write-Host " Starting drop table operation:" $tb
            Write-Host
            Write-Host

            ## Drop table after unloading and report any errors:
            &{
                trap { [System.DateTime]::Now.ToString("T") + " ### ERROR ### $_ dropping table."; break } 
                    &{ $tb.Drop() };
             }

            Write-Host "*** SUCCESS *** dropped table:" $tb
            Write-Host
            Write-Host
        }
    ELSEIF ($LASTEXITCODE -eq 1)
        {
            Write-Host "### ERROR ### unloading table" $tb " to file!"
            Write-Host
            Write-Host " Skipping DROP TABLE operation."
            Write-Host
            Write-Host
            Exit
        }