SMO Enumerations in Powershell

Published 17 March 11 11:20 PM | MartinBell

In my Changing Database Collations post I mentioned the difficulty I had calling the Alter method on the database when specifying a termination clause. The examples in Books Online and MSDN were not at all relevant, and the solution was not immediately obvious (to me). In the end it turned out to be quite simple and version 2 of my Process-Database function is:

function Process-Database ( [string] $NewCollation ){
    try {
        $db.UserAccess = "Single" ;
        $db.Alter([Microsoft.SqlServer.Management.Smo.TerminationClause]::RollbackTransactionsImmediately) ;
        $db.Collation = $NewCollation ;
        $db.UserAccess = "Multiple" ;
        $db.Alter() ;
        Write-Log "End Process-Database" ; 
    } 
    catch { 
        $err = $Error[0].Exception ; 
        Write-Log "Error ruuning script $Sqlfile : " ; 
        Write-Log $err ; 
        Throw "Error ruuning script $Sqlfile" ; 
    }
}

Another Enumeration is the RecoveryModel with possible members/values of Full, Simple and BulkLogged. I posted the following community content on MSDN for approval:

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")  | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlEnum") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null

$srv = new-Object Microsoft.SqlServer.Management.Smo.Server("(local)") ;
$db = New-Object Microsoft.SqlServer.Management.Smo.Database($srv, "Test_SMO_Database") ;
$db.Create() ;
Write-Host $db.CreateDate ;
Write-Host "`r`n------------------------------------------`r`n";
$db.DatabaseOptions ;
$db.UserAccess = "Single" ;
$db.Alter([Microsoft.SqlServer.Management.Smo.TerminationClause]::RollbackTransactionsImmediately) ;
$db.DatabaseOptions.RecoveryModel = [Microsoft.SqlServer.Management.Smo.RecoveryModel]::Simple ;
$db.UserAccess = "Multiple" ;
$db.Alter() ;
Write-Host "`r`n------------------------------------------`r`n------------------------------------------`r`n" ;
$db.DatabaseOptions ;
$db.Drop();
Write-Host "`r`n------------------------------------------`r`n";

Filed under: ,

Comments

# Dew Drop – March 18, 2011 | Alvin Ashcraft's Morning Dew said on March 18, 2011 01:03 PM:

Pingback from  Dew Drop – March 18, 2011 | Alvin Ashcraft's Morning Dew

This Blog

SQL Blogs

Syndication