Technical Article

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

,

Example:

.\Script_Table_to_File.ps1 FooServer BarDB 'Foo Table' 'X:\Foo Dir\'

Notes:

This was merely a personal exersize that got a little bit beyond that. πŸ™‚

Tables and output paths that have spaces in them must be encapsulated in single-tick apostrophes on the command line call.

This script will produce two files in a datestamped subdirectory of the $outroot values (UNC paths will work as well). One script is the table schema, which includes DRI and permissions on the object. The second file is the actual data unloaded from the table in a pipe ("|") delimited format.

### NOTE ###: Once the table has been successfully scripted and unloaded it is dropped.

Currently this script only finds dbo owned objects. I suspect I'll need to re-write a good piece of it to find non-dbo objects....

This script will execute against any version of SQL server that I've tested (2000, 2005 & 2008).

###############################################################################
# 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
β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆ}

Rate

β˜… β˜… β˜… β˜… β˜… β˜… β˜… β˜… β˜… β˜…

You rated this post out of 5. Change rating

Share

Share

Rate

β˜… β˜… β˜… β˜… β˜… β˜… β˜… β˜… β˜… β˜…

You rated this post out of 5. Change rating