Wednesday, September 3, 2008

Use PowerShell to capture database schema

Here is a PowerShell script to capture a database schema. Output is written to a directory/datetime file. Multiple databases/servers can be specified via the XML input file.

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:

Unknown said...

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

Unknown said...

Paul, Also good to note that since this script uses XElement, Linq is required, therefore .Net 3.5 is required.

Can't RDP? How to enable / disable virtual machine firewall for Azure VM

Oh no!  I accidentally blocked the RDP port on an Azure virtual machine which resulted in not being able to log into the VM anymore.  I did ...