[29] Window PowerShell DSC 学习系列----如何备份数据库和解决一个MS Server 2008上的大坑？

最新推荐文章于 2022-12-24 10:54:40 发布

置顶大象无形，大音希声

最新推荐文章于 2022-12-24 10:54:40 发布

阅读量2.9k

点赞数 1

本文链接：https://blog.csdn.net/chancein007/article/details/72705669

版权

1.1 PowerShell DSC 专栏收录该内容

33 篇文章 30 订阅

订阅专栏

有的时候我们需要定期的备份数据库，而DSC刚好就能非常好的做这个事情。DSC中有一个xSQLServer Module，里面提供了许多操作数据库的方法。但是里面没有任何一个DSC Resource能够备份数据库；所有只能通过另外一种比较委婉的方式：xSQLServerScript的DSC Resource来执行SQL语句而备份数据库。

xSQLServerScript提供了下面的参数：

      [Parameter(Mandatory = $true)]
        [System.String]
        $ServerInstance,

        [Parameter(Mandatory = $true)]
        [System.String]
        $SetFilePath,

        [Parameter(Mandatory = $true)]
        [System.String]
        $GetFilePath,

        [Parameter(Mandatory = $true)]
        [System.String]
        $TestFilePath,

        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]
        $Credential,

        [System.String[]]
        $Variable

下面提供一个具体使用的例子：

<#
.EXAMPLE
    This example shows how to run SQL script using SQL Authentication.
#>

Configuration Example
{
    param(
        [Parameter(Mandatory = $true)]
        [PSCredential]
        $SqlCredential
    )

    Import-DscResource -ModuleName xSQLServer

    Node localhost
    {
        xSQLServerScript 'RunSQLScript'
        {
            ServerInstance = 'localhost\SQL2016'
            Credential = $SqlCredential

            SetFilePath = 'C:\DSCTemp\SQLScripts\Set-RunSQLScript.sql'
            TestFilePath = 'C:\DSCTemp\SQLScripts\Test-RunSQLScript.sql'
            GetFilePath = 'C:\DSCTemp\SQLScripts\Get-RunSQLScript.sql'
            Variable = @("FilePath=C:\temp\log\AuditFiles")
        }
    }
}

备份SQL数据的脚本可以写在：Test-RunSQLScript.sql中，比如：

backup database TestDSC to disk = 'device.bak'

恭喜你，你完成了数据库的备份。但是不要高兴的太早，上面生成的DSC MOF文件在MS SQL Server 2012 以及以上版本都能正常运行，但是在MS SQL Server 2008 R2版本以前是运行不成的。其会报出类似于下面的这些错误提示：

VERBOSE: Perform operation 'Invoke CimMethod' with following parameters, ''methodName' = SendConfigurationApply,'className' = MSFT_DSCLocalConfigurationManage
r,'namespaceName' = root/Microsoft/Windows/DesiredStateConfiguration'.
VERBOSE: An LCM method call arrived from computer sqlservername with user sid S-1-5-21-4070628030-840234078-891559575-1109.
VERBOSE: [sqlservername]: LCM: [ Start Set ]
VERBOSE: [sqlservername]: [DSCEngine] Importing the module C:\Program Files\WindowsPowerShell\Modules\xSQLServer\7.0.0.0\DscResour
ces\MSFT_xSQLServerScript\MSFT_xSQLServerScript.psm1 in force mode.
VERBOSE: [sqlservername]: LCM: [ Start Resource ] [[xSQLServerScript]BackUpDB]
VERBOSE: [sqlservername]: LCM: [ Start Test ] [[xSQLServerScript]BackUpDB]
VERBOSE: [sqlservername]: [[xSQLServerScript]BackUpDB] Importing the module MSFT_xSQLServerScript in force mode.
VERBOSE: [sqlservername]: [[xSQLServerScript]BackUpDB] 2017-05-05_10-54-03: Module SqlServer not found, trying to use older SQLPS
module.
Unable to find type [Microsoft.SqlServer.Management.PowerShell.SqlPowerShellSqlExecutionException].
+ CategoryInfo : InvalidOperation: (Microsoft.SqlSe...cutionException:) [], CimException
+ FullyQualifiedErrorId : TypeNotFound
+ PSComputerName : localhost

VERBOSE: [sqlservername]: LCM: [ End Test ] [[xSQLServerScript]BackUpDB] in 0.4060 seconds.
The PowerShell DSC resource '[xSQLServerScript]BackUpDB' with SourceInfo '::16::9::xSQLServerScript' threw one or more non-terminating errors while running
the Test-TargetResource functionality. These errors are logged to the ETW channel called Microsoft-Windows-DSC/Operational. Refer to this channel for more
details.
+ CategoryInfo : InvalidOperation: (:) [], CimException
+ FullyQualifiedErrorId : NonTerminatingErrorFromProvider
+ PSComputerName : localhost

VERBOSE: [sqlservername]: LCM: [ End Set ]
The SendConfigurationApply function did not succeed.
+ CategoryInfo : NotSpecified: (root/Microsoft/...gurationManager:String) [], CimException
+ FullyQualifiedErrorId : MI RESULT 1
+ PSComputerName : localhost

VERBOSE: Operation 'Invoke CimMethod' complete.
VERBOSE: Time taken for configuration job to complete is 4.183 seconds