PowerShell应用之(SMO) 类库

SMO类库描述

在命名空间Microsoft.SqlServer.Management.Smo下提供有丰富的类库，来描述SQL Server数据库引擎核心对象，包含实例(instances)、数据库(databases),表(tables),存储过程(stored procedures)和视图(views)。它能为我们实现：

连接SQL Server实例
查询和修改实例设置(instance settings)和配置选项(configuration options)
查询和修改数据库对象
在SQL Server实例执行DDL(data definition language)操作
生成数据库对象脚本
执行数据库维护任务，如备份和还原操作

下面我们测试其中一两个例子描述它的应用。

连接SQL Server实例

连接SQL Server实例，我们需要应用到命名空间Microsoft.SqlServer.Management.Smo空间下的Server类。

e.g.<Scripts01>

$serverInstance="WINSERVER01\SQL2008DE01" #SQL Server实例名
$userName="sa" #登录账号
$password="sql20081" #登录密码

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null #加载程序集Microsoft.SqlServer.Smo
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null  #加载程序集Microsoft.SqlServer.ConnectionInfo
$ServerConnection =new-object "Microsoft.SqlServer.Management.Common.ServerConnection" $serverInstance,$userName, $password 
$Server=New-Object "Microsoft.SqlServer.Management.Smo.Server" $ServerConnection

Try
{
    $ServerConnection.Connect()
    Write-Host "实例" $serverInstance "连接OK！" 
}
Catch
{
    Write-Error "实例" $serverInstance "无法连接！" 
}

<Scripts01>中代码：

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null #加载程序集Microsoft.SqlServer.Smo
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null #加载程序集Microsoft.SqlServer.ConnectionInfo

描述加载的程序集，我们在应用命名空间Microsoft.SqlServer.Smo下的各个类库都需要先加载对应的程序集。如这里用到的类Microsoft.SqlServer.Management.Smo.Server对应的程序集是Microsoft.SqlServer.Smo,类Microsoft.SqlServer.Management.Common.ServerConnection，对应的程序集是Microsoft.SqlServer.ConnectionInfo。

我这里看到

[System.Reflection.Assembly]::Load()无法应用。在PowerShell 2.0中反而应用到

[System.Reflection.Assembly]::LoadWithPartialName()

看MSDN的时候，提到

[System.Reflection.Assembly]::LoadWithPartialName()

在.Net Framework 2.0之后已过时。这是我感觉比较纠结的位置。

查询和修改实例设置(instance settings)和配置选项(configuration options)

当我们连接上SQL Server实例后，我们可以提供类Server提供的属性Settings和属性configuration options，来修改设置和配置选项。我例子沿用上边的例子，继续。

e.g.<Scripts02>查询部分：

if ($ServerConnection.IsOpen -eq $true)
{
    $Settings=$Server.Settings
    $Configuration=$Server.Configuration
    
    #显示设置
    $Settings
    
    #显示配置选项
    "MinServerMemory : " + [int]$Configuration.MinServerMemory.RunValue +"MB"
    "MaxServerMemory : " + [int]$Configuration.MaxServerMemory.RunValue +"MB"
    "IsSqlClrEnabled : " + [boolean]$Configuration.IsSqlClrEnabled.RunValue
    "RemoteDacConnectionsEnabled : " + [boolean]$Configuration.RemoteDacConnectionsEnabled.RunValue
    
    
}

这里只是列出settings和configuration options的一小部分内容。如果想了解更多的信息可以通过get-member获取对应的属性，再输出到PowerShell控制台。我们不引可以查询settings和configuration options的内容，还可以修改它们的内容。

e.g.<Scripts03>修改部分：

#修改设置
$Settings.DefaultFile="E:\DATA" #修改前是“ E:\DATA\SQL2008DE01”
$Settings.DefaultLog="E:\DATA" #修改前是“ E:\DATA\SQL2008DE01”

$Configuration.IsSqlClrEnabled.ConfigValue=0 #修改前是1
$Server.Alter()

<Scripts03>如果我们使用的是编辑器是Windows PowerShell ISE，一样可以执行所选的部分代码。

<Scripts03>代码中，说明一点修改设置的时候，最后必须调用实例对象$Server的Alter()方法，不然不会应用到SQL Server实例中。我们这里可以检查修改后的实例信息：

实际应用例子

这里是一个实际中的例子，脚本的功能，主要是查询出某一个实例中的默认数据库路径，实例登录账号、数据库列表。

<#===========================================#>

$serverInstance="WINSERVER01\SQL2008DE01" 
$userName="sa"
$password="sql20081"


<#===========================================#>
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null

$ServerConnection =new-object "Microsoft.SqlServer.Management.Common.ServerConnection" $serverInstance,$userName, $password  

Try
{$ServerConnection.Connect()}
Catch
{Write-Error $_}

if($ServerConnection.IsOpen -eq $True)
{    

    $Server=New-Object "Microsoft.SqlServer.Management.Smo.Server" $ServerConnection
    Write-Host
    Write-Host "Server Name  :" $Server.Name
    Write-Host
    Write-Host "======================= Settings ================================"
    Write-Host "DefaultFile  : " $Server.Settings.DefaultFile
    Write-Host "DefaultLog   : " $Server.Settings.DefaultLog
    Write-Host

    Write-Host "======================= Configuration ================================"
    "XPCmdShellEnabled              : "+ [boolean]$Server.Configuration.XPCmdShellEnabled.RunValue
    "DatabaseMailEnabled            : "+ [boolean]$Server.Configuration.DatabaseMailEnabled.RunValue
    "IsSqlClrEnabled                : "+ [boolean]$Server.Configuration.IsSqlClrEnabled.RunValue
    "SqlMailXPsEnabled              : "+ [boolean]$Server.Configuration.SqlMailXPsEnabled.RunValue
    "DatabaseMailEnabled            : "+ [boolean]$Server.Configuration.DatabaseMailEnabled.RunValue
    "OleAutomationProceduresEnabled : "+ [boolean]$Server.Configuration.OleAutomationProceduresEnabled.RunValue
    "AdHocDistributedQueriesEnabled : "+ [boolean]$Server.Configuration.AdHocDistributedQueriesEnabled.RunValue
    Write-Host
    Write-Host "======================= Logins ================================"
    $Server.Logins | Format-Table -AutoSize -Wrap -Property Name,CreateDate,IsDisabled,LoginType,IsSystemObject

    Write-Host
    Write-Host "======================= DataBases ================================"
    $Server.Databases| Sort-Object -Property @{Expression="IsSystemObject";Descending=$True},@{Expression="ID";Ascending=$True}  | Format-Table -AutoSize -Wrap -Property Name,IsSystemObject,Size,Status,CreateDate,PrimaryFilePath
}

小结

以上是举例子简单描述如何通过PowerShell 2.0，调用命名空间Microsoft.SqlServer.Management.Smo下的类库，管理数据库实例。在真实的场景中，我们碰到更复杂的应用。不管如何，我们需要应用到SMO类库，具体的用法可以参阅：http://technet.microsoft.com/en-us/library/gg720307.aspx