Day: November 5, 2011

Script Database Objects with Powershell

Here’s a quick script to script out all database objects using powershell.  This was adapted (heavily) from the Hey Scripting Guy! blog script here.  This also stores the password credential in a file on the server.  You’ll need to generate the file before running the script.  Remember to generate the file under the account that will be executing the script, as the contents of the file will be encrypted using the account that generated it.  Use this script to create the file:

read-host -assecurestring | convertfrom-securestring | out-file C:\Testcredentials.txt

And, here is the script to script out the objects.  I use this to create a backup of the schema daily in case any changes need to be rolled back.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.smo') | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Management.Common') | out-null

$servers = Get-Content c:TestServers.txt
$savePath = "C:TestScriptDB"
$Error.Clear()
cls

$currDate = get-date -format yyyyMMdd

foreach($server in $servers)
{
	$password = get-content C:Testcredentials.txt | convertto-securestring
	$srvConn = New-Object('Microsoft.SqlServer.Management.Common.ServerConnection') ($server, 'LogonName', $password)
	$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $srvConn
	$scriptr = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($srv)
	$scriptr.Options.AppendToFile = $True
	$scriptr.Options.AllowSystemObjects = $False
	$scriptr.Options.ClusteredIndexes = $True
	$scriptr.Options.DriAll = $True
	$scriptr.Options.ScriptDrops = $False
	$scriptr.Options.IncludeHeaders = $True
	$scriptr.Options.ToFileOnly = $True
	$scriptr.Options.Indexes = $True
	$scriptr.Options.Triggers = $true
	$scriptr.Options.Permissions = $True
	$scriptr.Options.WithDependencies = $False
	try
	{

		foreach($database in $srv.Databases | where{!($_.IsSystemObject)})
		{
			$dbName = $database.Name
			$serverName = $server.Replace("","-")

			if(!(Test-Path $savePath$serverName$currDate$dbName))
			{
				New-Item -ItemType directory -Name $serverName$currDate$dbName -Path $savePath
			}

			$dbObj = $database.Tables
			$dbObj += $database.Views
			$dbObj += $database.StoredProcedures | where{!($_.IsEncrypted)}
			$dbObj += $database.UserDefinedFunctions | where{!($_.IsEncrypted)}

			foreach ($scrptObj in $dbObj | where {!($_.IsSystemObject)})
			{
				$TypeFolder = $scrptObj.GetType().Name
				if ((Test-Path -Path "$savePath$serverName$currDate$dbName$TypeFolder") -eq "true")
				{
					"Scripting Out $TypeFolder $scrptObj"
				}
				else
				{
					new-item -type directory -name "$TypeFolder" -path "$SavePath$serverName$currDate$dbName"
				}
				$ScriptFile = $scrptObj -replace "[|]"
				$scriptr.Options.FileName = "$savePath$serverName$currDate$dbName$TypeFolder$ScriptFile.sql"
				$scriptr.Script($scrptObj)
			}

		}
	}
	catch
	{
		foreach($err in $Error)
		{
			Write-Host $err
			write-host $err.ErrorDetails
			write-host $err.Exception
			Write-Host $err.Exception.InnerException
			Write-Host $err.Exception.StackTrace
		}
	}
}