数据库比较工具SQL Compare：如何使用SQL Snapper实现SQL Compare Snapshots的自动化？

如果您有SQL Compare，那么SQL Snapper实用程序对于某些团队活动来说是非常有价值的“额外”功能，因为它可以自由分发。这意味着任何开发人员都可以从其本地工作站上的数据库创建SQL比较快照，并将其存储在 络上。

您将在SQL Compare安装目录中找到RedGate.SQLSnapper.exe（类似于C： Program Files（x86） Red Gate SQL Compare xx，其中xx是您当前的SQL Compare版本）。SQL Snapper有自己的交互式GUI，您也许可能更可能从脚本中使用它。

如何分发SQL Snapper/strong>

如果需要将Snapper放置在工作站上，则需要从同一目录中复制可执行文件以及System.Threading.dll和RedGate.SOCCompareInterface.dll文件。

自动化SQL快照程序入门

从PowerShell运行snapper并不难。基本上，如果您很幸运并且对服务器具有Windows身份验证，并且不必太担心理解错误，则可以执行此操作（实际别名取决于您当前的SQL Compare版本）：

Set-Alias Snapper "${env:ProgramFiles(x86)}Red GateSQL Compare 14Redgate.SQLSnapper.exe"Snapper /server:MyServerOrInstance /database:MyDatabase /MakeSnapshot:"$env:HOMEDRIVE$env:HOMEPATHdocumentsMyDatabase.snp"if ($ { "successfully produced snapshot of  MyDatabase" }

或者，如果您需要SQL Server身份验证：

Set-Alias Snapper "${env:ProgramFiles(x86)}Red GateSQL Compare 14Redgate.SQLSnapper.exe"Snapper /server:MyServerOrInstance /database:MyDatabase /MakeSnapshot:"$env:HOMEDRIVE$env:HOMEPATHdocumentsMyDatabase.snp" /username:MyUserName /password:MySecretPasswordif ($ { "successfully produced snapshot of  MyDatabase" }

生成单个数据库的快照

这是用于从PowerShell运行快照程序的更有用的脚本。我已经处理了错误处理以及偶尔需要用户名和密码的问题。

Set-Alias Snapper "${env:ProgramFiles(x86)}Red GateSQL Compare 14Redgate.SQLSnapper.exe"$ErrorMeanings = #all the possible snapper errors.@{    "0" = "Success"; "1" = "General error"; "3" = "Illegal argument duplication"    ; "8" = "Unsatisfied argument dependency";; "32" = "Value out of range"    ; "33" = "Value overflow"; "34" = "Invalid value"; "64" = "Command line usage error"    ; "65" = "Data error"; "69" = "Resource unavailable"; "70" = "An unhandled exception occurred"    ; "73" = "Failed to create report"; "74" = "I/O error"; "77" = "Insufficient permission"    ; "126" = "SQL Server error"; "130" = "Ctrl-Break"; }#to get help with the CLI for Snapper try#  snapper /verbose /#-----We need to fill in these four essential parameters ------$SourceServer = 'MyServerOrInstance' # the name of the server or instance$SourceDatabase = 'MyDatabase' # the name of the database$username='MyUserName' # leave blank if Windows Authentication# and finally the name of the database$snapshotLocation = "$env:HOMEDRIVE$env:HOMEPATHdocuments$SourceDatabase.snp"<# Snapper over-writes existing Snapshot file #><# we'll do splatting because this makes it easier to add credentials when necessary #>$AllArgs = @{ 'server' = "$SourceServer";              'Database' = "$SourceDatabase";              'makesnapshot' = "$snapshotLocation" }if ($username -ne '') #then it is using SQL Server Credentials{ # we see if we've got these stored already    $SqlEncryptedPasswordFile = "$env:USERPROFILE$($username)-$SourceServer.xml"    # test to see if we know about the password in a secure string stored in the user area    if (Test-Path -path $SqlEncryptedPasswordFile -PathType leaf)    {        #has already got this set for this login so fetch it        $SqlCredentials = Import-CliXml $SqlEncryptedPasswordFile    }    else #then we have to ask the user for it (once only)    {        # hasn't got this set for this login        $SqlCredentials = get-credential -Credential $UserName        # Save in the user area        $SqlCredentials | Export-CliXml -Path $SqlEncryptedPasswordFile        <# Export-Clixml only exports encrypted credentials on Windows.        otherwise it just offers some obfuscation but does not provide encryption. #>    }    #now add the credentials to SQL Snapper    $AllArgs += @{        'username' = "$($SqlCredentials.UserName)";        'password' = "$($SqlCredentials.GetNetworkCredential().password)"    }}Snapper @allArgsif ($ { "successfully produced snapshot of  $SourceServer.$SourceDatabase in $snapshotLocation " }else{    #if there was an error of some sort     $SoFarSoGood = $false;    $SQLCompareError = "SQK Snapper had an error creating snapshot of $SourceServer.$SourceDatabase in $snapshotLocation ! (code $LASTEXITCODE) - $(        $ErrorMeanings."$lastexitcode")"    Write-warning $SQLCompareError }

在批处理文件中运行快照程序

如果您的环境不鼓励使用PowerShell脚本，或者出于安全原因禁止使用PowerShell脚本，则完全可以使用批处理文件来运行SQL Snapper。

"%ProgramFiles(x86)%\Red GateSQL Compare 14Redgate.SQLSnapper.exe" /server:%sourceServer% /database:%SourceDatabase%  %SourceSQLSnapperCredentials% /Makesnapshot:%SnapShotPath%

当然，您需要为所有这些变量分配值。由于我不喜欢在脚本中包含用户ID或密码，因此将它们存储在用户区中。这使脚本编写变得更加复杂，因此以下示例可能看起来有些过分设计

@echo offVERIFY errors 2>nulSETLOCAL ENABLEDELAYEDEXPANSIONSETLOCAL ENABLEEXTENSIONSSet outcome=did our bestif ERRORLEVEL 1 (   echo could not set local execution environment   goto bombsite   )Rem set Source to the database you wish to take a snapshot ofSet SourceDatabase=MyDatabaseRem Set SourceServer to the name of the server or instance containing this databaseSet SourceServer=MyServerOrInstanceRem Specify where you would like to save the snapshot fileSet SnapShotPath="%HOMEDRIVE%%HOMEPATH%documents%SourceDatabase%.snp"Rem We now to check if you have provided credentialsREM before you start. You need to write out your SQLCMD credentials to a file in your userrem area using code like this, ONLY if you use SQL Server Credentials.Rem so uncomment these next six lines if you need credentials and fill in your server credentialsRem echo MyUserID/MyPassword>%userProfile%%SourceServer:=_%SQLSnapper.txtRem if ERRORLEVEL 1 (Rem   echo Could not write Source CredentialsREM   goto bombsiteREM   )remREM read in your Source SQLCMD command and credentials if you have anyif exist %userProfile%%SourceServer:=_%SQLSnapper.txt (   Set /p SourceCredentials=<%userProfile%%SourceServer:=_%SQLSnapper.txtif ERRORLEVEL 1 (  echo Could not read in Source Credentials  goto bombsite))Rem Parse the source credentials into two variablesset "Sourceuid=%SourceCredentials:/=" & set "SourcePw=%"REM credentials are presented in two different ways by the CLI appsSet SourcesqlcmdCredentials= /U %Sourceuid% /P %SourcePw%Set SourceSQLSnapperCredentials= /username:%Sourceuid% /Password:%SourcePw%REM Set source credentials correctly for windows securityIF NOT DEFINED  SourceCredentials (  Set SourcesqlcmdCredentials =  set SourceSQLSnapperCredentials = ) echo Creating Snapshot from database %SourceDatabase% on %sourceServer%  as %Source   "%ProgramFiles(x86)%\Red GateSQL Compare 14Redgate.SQLSnapper.exe" /server:%sourceServer% /database:%SourceDatabase%  %SourceSQLSnapperCredentials% /Makesnapshot:%SnapShotPath%   SET outcome=created snapshot %SnapShotPath% from  %SourceDatabase% on %sourceServer%if ERRORLEVEL 1 (   echo Could not create snapshot   goto bombsite   )goto end:bombsiteREM This is where the program goes if a problem is detectedcolor 04Echo Sadly, we failed, though we %outcome%!echo onENDLOCALcolorExit /b 1:endREM This is where we go if all ended well.Echo we %outcome% and finished successfullyENDLOCALecho onExit /b 0

未完待续……下一章将继续讲解

标签：