Looking at PowerShell exception handling

With PowerShell v2 we were given a more complete exception handling than the trap-handling in PowerShell v1.
This is a quick spike on throwing an exception, catching it and looking at the $Error variable.
Feel free to continue :-)
Clear-Host


$Error.Clear()


try {
  #throw "MyException"
  #throw [System.DivideByZeroException]
  #throw [IO.PathTooLongException]
  throw [System.Data.NoNullAllowedException]
}
catch {
  $_.CategoryInfo
}
finally {
  ":: Final"
}


if ($Error) {
  "{0:s}  Error in script execution. See log for details." -f $([System.DateTime]::Now)
}


The output of the script above is
Category   : OperationStopped
Activity   : 
Reason     : RuntimeException
TargetName : System.Data.NoNullAllowedException
TargetType : RuntimeType


:: Final
2012-01-02T22:15:20  Error in script execution. See log for details.

More details and links is in the article "Windows PowerShell Error Records" on MSDN Windows Dev Center.
Please notice that the returned objects are not of the System.Exception class but of the ErrorRecord class.

Check database using SMO

For a long time I have checked the databases by DBCC CHECKDB, but I would like to check the database in PowerShell with SMO to include the check in a more general maintenance script with additional features.

The check can be done with the SMO Database class method CheckTables().
[string]$ServerName = 'DNDBA01.dn.lan'
$Server = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerName
$Server.Databases | ForEach-Object {
"{0:s}  Checking database [$($_.Name)]..." -f $([System.DateTime]::Now)
$_.CheckTables([Microsoft.SqlServer.Management.Smo.RepairType]::None, [Microsoft.SqlServer.Management.Smo.RepairOptions]::AllErrorMessages)
}

All errormessages are included to help the administrator if a check fail. Also it can be used for analytics.

Additional features to the script can be considered after reading the discussion by Cindy Gross.

A nice introduction to checking database integrity with SMO is
"Getting Started with SMO in SQL 2005 - Integrity Checks" by Jasper Smith.

Red-Gate SQL Compare with PowerShell

I would like to compare the structure of two databases as in development vs production.
The tool SQL Compare from Red-Gate I have used on a daily basis, and I want similar result without the GUI, so that I can automate a comparison.
In the SQL Toolbelt there are beside SQL Compare a SQL Comparison SDK, but it is a version behind SQL Compare. One thing I would miss is the possibility to compare with a backup or a scriptfile.
By looking into the C# examples of the SDK and the assemblies with Visual Studio Object Browser, this (very) simple example gives a usable result. Please consider the example as a technical sprint, not a final solution.

# Generate path for Red-Gate SQL Compare assemblies
$SqlComparePath = "\Red Gate\SQL Compare 9"
# True if the OS is 64 bit (x64)
if([IntPtr]::Size -eq 8) { $SqlComparePath = ${env:ProgramFiles(x86)} + $SqlComparePath }
else { $SqlComparePath = ${env:ProgramFiles} + $SqlComparePath }
# Add Red-Gate Compare assembly
Add-Type -Path "$SqlComparePath\RedGate.SQLCompare.Engine.dll"
# Register staging database
[string]$stagingServerName = 'SQLDEV42.sqladmin.lan'
[string]$stagingDbName = 'sqladmin_repository'
$stagingConnectionProperties = New-Object RedGate.SQLCompare.Engine.ConnectionProperties $stagingServerName, $stagingDbName
$stagingDb = New-Object RedGate.SQLCompare.Engine.Database
$stagingDb.Register( $stagingConnectionProperties, [RedGate.SQLCompare.Engine.Options]::Default )
# Register Production database
[string]$prodServerName = 'SQLPRD42.sqladmin.lan'
[string]$prodDbName = 'sqladmin_repository'
$prodConnectionProperties = New-Object RedGate.SQLCompare.Engine.ConnectionProperties $prodServerName, $prodDbName
$prodDb = New-Object RedGate.SQLCompare.Engine.Database
$prodDb.Register( $prodConnectionProperties, [RedGate.SQLCompare.Engine.Options]::Default )
# Compare databases. Result in RedGate.SQLCompare.Engine.Difference object
$stagingDb.CompareWith( $prodDb, [RedGate.SQLCompare.Engine.Options]::Default ) |
Format-Table -Property Type,DatabaseObjectType,Name -AutoSize

The result is something like this:
 Type DatabaseObjectType Name
 ---- ------------------ ----
Equal              Table [sqladmin].[collation]
Equal              Table [sqladmin].[computer]
Equal              Table [sqladmin].[database]
Equal              Table [sqladmin].[database_history]
Equal              Table [sqladmin].[department]
Equal              Table [sqladmin].[edition]
Equal              Table [sqladmin].[environment]
Equal              Table [sqladmin].[file]
Equal              Table [sqladmin].[filegroup]
Equal              Table [sqladmin].[security]
Equal              Table [sqladmin].[ssdb]
Equal              Table [sqladmin].[version]
Equal               Role public
Equal               Role sqlanchor_user
Equal               Role db_owner
Equal               Role db_accessadmin
Equal               Role db_securityadmin
Equal               Role db_ddladmin
Equal               Role db_backupoperator
Equal               Role db_datareader
Equal               Role db_datawriter
Equal               Role db_denydatareader
Equal               Role db_denydatawriter
Equal             Schema dbo
Equal             Schema guest
Equal             Schema INFORMATION_SCHEMA
Equal             Schema sys
Equal             Schema sqladmin
Equal             Schema sqlanchor
Equal             Schema db_owner
Equal             Schema db_accessadmin
Equal             Schema db_securityadmin
Equal             Schema db_ddladmin
Equal             Schema db_backupoperator
Equal             Schema db_datareader
Equal             Schema db_datawriter
Equal             Schema db_denydatareader
Equal             Schema db_denydatawriter
Equal               View [sqladmin].[v_computer]
Equal               View [sqladmin].[v_database]
Equal               View [sqladmin].[v_ssdb]
Equal               View [sqladmin].[v_ssdb_full]
Equal    StoredProcedure [sqlanchor].[ssdb_version-get]
Equal    StoredProcedure [sqlanchor].[environment-get]
Equal    StoredProcedure [sqlanchor].[environment_detail-get]
Equal    StoredProcedure [sqlanchor].[computer_summary-get]
...

Before you use the assemblies, take a look at the Red-Gate license agreement.

Script a database definition

Recreating a database in a higher environment is a common task. Quite often it is not that smart just to restore a back, as you then get database users, file sizes, obsolete schemas etc into the higher environment.
Scripting out a database can be done from SQL Server Management Studio, but this is a manual operation. When you are serving a small army of developers, they need a definition of the production database on a regular basis. Doing that by SQL Server Management Studio quickly gets boring.
There are third party tools like Red Gate SQL Compare that can generate a schema snapshot or a direct comparison, but such a tool is expensive for a simple task like this. In many other situations I am really happy with such tools, but that is another story.
This PowerShell script generates the T-SQL script to create a database and its structure. The script can be edited and reviewed before execution, and can serve as documentation in a Change procedure.
[string]$SqlServerName = '(local)'
[string]$DbName = 'AdventureWorks'

[string]$SqlBatchSeperator = 'GO'  # SQL Server Management Studio default: GO

"/*`nT-SQL script to define the structure of the database '$($DbName)' on the server '$($SqlServerName)'."
"Script generated $($(Get-Date).ToUniversalTime().ToString('s')) UTC"
"  by the user $([Security.Principal.WindowsIdentity]::GetCurrent().Name)"
"  on the computer $($env:COMPUTERNAME)"
"  with the script file '$($MyInvocation.InvocationName)'"
"*/"

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | Out-Null
$Server = New-Object Microsoft.SqlServer.Management.Smo.Server $SqlServerName
$Options = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$Options.IncludeHeaders = $true
$Options.Permissions = $true

"`n--*** Logins ***"
$Server.Logins | Where-Object { ($_.Name -ne 'sa') -and ($_.Name -notlike '##MS_Policy*') -and ($_.Name -notlike 'NT *') } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n--*** Database definition ***"
$Server.Databases[$DbName].Script();$SqlBatchSeperator
"`n--EXECUTE [$DbName].dbo.sp_changedbowner @loginame = N'sa', @map = true;`n$SqlBatchSeperator"
"`nALTER AUTHORIZATION ON DATABASE::[$DbName] TO [sa];`n$SqlBatchSeperator"
"`nUSE [$DbName];`n$SqlBatchSeperator"
"`n`n--*** Userdefined applicationroles ***"
$Server.Databases[$DbName].ApplicationRoles | Where-Object { $_.IsFixedRole -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined databaseroles ***"
$Server.Databases[$DbName].Roles | Where-Object { $_.IsFixedRole -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined schemas ***"
$Server.Databases[$DbName].Schemas | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }

$Options.IncludeDatabaseRoleMemberships = $true

"`n`n--*** Database users ***"
$Server.Databases[$DbName].Users | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }

$Options.DriAll = $true
$Options.Indexes = $true

"`n`n--*** Userdefined assemblies ***"
$Server.Databases[$DbName].Assemblies | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined certificates ***"
$Server.Databases[$DbName].Certificates | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined defaults ***"
$Server.Databases[$DbName].Defaults | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined fulltext catalogs ***"
$Server.Databases[$DbName].FullTextCatalogs | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined rules ***"
$Server.Databases[$DbName].Rules | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined stored procedures ***"
$Server.Databases[$DbName].StoredProcedures | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined tables with indexes ***"
$Server.Databases[$DbName].Tables | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined Triggers ***"
$Server.Databases[$DbName].Triggers | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined functions ***"
$Server.Databases[$DbName].UserDefinedFunctions | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined datatypes ***"
$Server.Databases[$DbName].UserDefindTypes | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined views ***"
$Server.Databases[$DbName].Views | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }

The script covers the basics, and I think that it is easy to fit it to a equal task.

The script can be used in a Visual Studio solution to compare with a database in development. Such a comparison can give a deployment script for a higher and isolated environment.

Reference

TechNet Library: SMO "Server Class"

PowerShell 3 CTP

The preview is officially released with "Windows Management Framework 3.0 Community Technology Preview". The details are described in a entry on the Windows PowerShell Blog:
Windows Management Framework 3.0 Community Technology Preview (CTP) #1 Available for Download

The autumn looks to be most interesting :-)