Microsoft® SQL Server® 2012 Bible

795

Chapter 30: Confi guring and Managing SQL Server with PowerShell

30

By default, the scripting process sends the results to the console, so setting the

ToFileOnly property to True causes the scripter to send the script only to the fi le speci-

fi ed. Setting the ClusteredIndexes property to True causes the clustered index for a

table to be included in the script, and setting the Indexes property to True causes the

nonclustered indexes to be included in the script. The DriAll property, when set to True,

causes all objects with enforced declarative referential integrity to be included in the script.

The objects to be scripted need to be added to an array of type SqlSmoObject. After the

array has been populated with all the objects to be scripted, invoke the Script method

and the script will be created. Listing 30-6 shows the script to create a T-SQL script of all

tables in the AdventureWorks2012 database.

LISTING 30-6 Scripting.ps1

function Export-DBObjectsIntoFolders(

[string]$dbname, [string]$server, [String]$Path,

[string]$outputFilename=""){

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

$SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $server

$db = $SMOserver.databases[$dbname]

<#Build this portion of the directory structure out here in case

scripting takes more than one minute.#>

$SavePath = "C:\TEMP\Databases\" + $($dbname)

$DateFolder = get-date -format yyyyMMddHHmm

if (!$Path) {$Path="$SavePath\$DateFolder"}

else {

if ((Test-Path -Path "$Path") -eq "true") `

{"Scripting Out to $Path $DateFolder"} `

else {new-item -type directory -name "$DateFolder"-path "$SavePath"}

}

$Objects = $db.Tables

foreach ($ScriptThis in $Objects | where {!($_.IsSystemObject)}) {

<#Script the DRI too#>

$ScriptDrop = new-object ('Microsoft.SqlServer.Management.Smo.Scripter')

($SMOserver)

$ScriptDrop.Options.AppendToFile = $True

$ScriptDrop.Options.AllowSystemObjects = $False

$ScriptDrop.Options.ClusteredIndexes = $False

$ScriptDrop.Options.DriAll = $False

$ScriptDrop.Options.ScriptDrops = $False

$ScriptDrop.Options.IncludeHeaders = $True

$ScriptDrop.Options.ToFileOnly = $True

$ScriptDrop.Options.Indexes = $False

Continues

c30.indd 795c30.indd 795 7/31/2012 9:46:24 AM7/31/2012 9:46:24 AM

http://www.it-ebooks.info