To run: ./CaptureSchema.ps1 databases.xml
Here is the PowerShell code:
param ([string]$xmlConfig = $(throw '%argument 1 must be XML configuration file path'))
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("System.Data") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("System.Core") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("System.Linq") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("System.Xml.Linq") | out-null
function ScriptDatabase([string]$serverName, [string]$dbName)
{
$fileName = [String]::Format("{0} {1}.sql", [DateTime]::Now.ToString("yyyyMMdd_HHmmss"), $dbName)
[Console]::Write("Server: $serverName, Database: $dbName, Output: `"$fileName`" . . . ")
$srv = new-object "Microsoft.SqlServer.Management.SMO.Server" $serverName
$db = new-object "Microsoft.SqlServer.Management.SMO.Database"
$scr = New-Object "Microsoft.SqlServer.Management.Smo.Scripter"
$db = $srv.Databases[$dbName]
$scr.Server = $srv
$options = New-Object "Microsoft.SqlServer.Management.SMO.ScriptingOptions"
$options.ClusteredIndexes = $true
$options.Default = $true
$options.DriAll = $true
$options.Indexes = $true
$options.IncludeHeaders = $true
$options.Triggers = $true
$options.AppendToFile = $false
$options.FileName = "$pwd\$fileName"
$options.ToFileOnly = $true
# output all db tables
$scr.Options = $options
$tables = $db.Tables
if ($tables -ne $null)
{
$scr.Script($db.Tables)
}
# output all sprocs
$options.AppendToFile = $true
$sprocs = $db.StoredProcedures | where {$_.IsSystemObject -eq $false}
if ($sprocs -ne $null)
{
$scr.Script($sprocs)
}
# output all db views
$views = $db.Views | where {$_.IsSystemObject -eq $false}
if ($views -ne $null)
{
$scr.Script($views)
}
"done."
}
function SaveSchema($xmlDb)
{
# make folder if not exists yet
$dbName = $xmlDb.Element("Name").Value
$dirName = ".\$dbName"
if ((Test-Path -path $dirName) -eq $False)
{
"Creating directory $dirName..."
ni -type directory $dirName | out-null
}
# save the schema
$serverName = $xmlDb.Element("Server").Value
$prevDir = $pwd
$prevDir
set-location $dirName
ScriptDatabase $serverName $dbName
set-location $prevDir
}
#
# main
#
$xml = [System.Xml.Linq.XElement]::Load((Resolve-Path "$xmlConfig"))
foreach($db in $xml.Elements("Database"))
{
if ($db.Attribute("Enabled").Value -eq $true)
{
SaveSchema $db
}
}
exit
Here is the XML input file:
<Databases>
<Database Enabled="true">
<Name>DatabaseName</Name>
<Server>ServerName</Server>
</Database>
<!-- repeat the Database element if more
than one database schema to capture -->
</Databases>
2 comments:
Paul, There really needs to be an easier way to get at the snippets you post.
Perhaps a download package link, giving a zipfile of all the snippets? Or something similar
Paul, Also good to note that since this script uses XElement, Linq is required, therefore .Net 3.5 is required.
Post a Comment