SQL Compar是一款比較和同步SQL Server數(shù)據(jù)庫結(jié)構(gòu)的工具。現(xiàn)有超過150,000的數(shù)據(jù)庫管理員、開發(fā)人員和測試人員在使用它。當(dāng)測試本地?cái)?shù)據(jù)庫，暫存或激活遠(yuǎn)程服務(wù)器的數(shù)據(jù)庫時(shí)，SQL Compare將分配數(shù)據(jù)庫的過程自動化。

下載SQL Compar

您將在SQL Compare安裝目錄中找到RedGate.SQLSnapper.exe（類似于C：\ Program Files（x86）\ Red Gate \ SQL Compare xx，其中xx是您當(dāng)前的SQL Compare版本）。SQL Snapper有自己的交互式GUI，您也許可能更可能從腳本中使用它。

如何分發(fā)SQL Snapper？

如果需要將Snapper放置在工作站上，則需要從同一目錄中復(fù)制可執(zhí)行文件以及System.Threading.dll和RedGate.SOCCompareInterface.dll文件。

自動化SQL快照程序入門

從PowerShell運(yùn)行snapper并不難。基本上，如果您很幸運(yùn)并且對服務(wù)器具有Windows身份驗(yàn)證，并且不必太擔(dān)心理解錯(cuò)誤，則可以執(zhí)行此操作（實(shí)際別名取決于您當(dāng)前的SQL Compare版本）：

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

Set-Alias Snapper "${env:ProgramFiles(x86)}\Red Gate\SQL Compare 14\Redgate.SQLSnapper.exe"
Snapper /server:MyServerOrInstance /database:MyDatabase /MakeSnapshot:"$env:HOMEDRIVE$env:HOMEPATH\documents\MyDatabase.snp" /username:MyUserName /password:MySecretPassword
if ($?) { "successfully produced snapshot of  MyDatabase" }

生成單個(gè)數(shù)據(jù)庫的快照

這是用于從PowerShell運(yùn)行快照程序的更有用的腳本。我已經(jīng)處理了錯(cuò)誤處理以及偶爾需要用戶名和密碼的問題。

Set-Alias Snapper "${env:ProgramFiles(x86)}\Red Gate\SQL Compare 14\Redgate.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:HOMEPATH\documents\$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 @allArgs
if ($?) { "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
    
}

在批處理文件中運(yùn)行快照程序

如果您的環(huán)境不鼓勵使用PowerShell腳本，或者出于安全原因禁止使用PowerShell腳本，則完全可以使用批處理文件來運(yùn)行SQL Snapper。

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

當(dāng)然，您需要為所有這些變量分配值。由于我不喜歡在腳本中包含用戶ID或密碼，因此將它們存儲在用戶區(qū)中。這使腳本編寫變得更加復(fù)雜，因此以下示例可能看起來有些過分設(shè)計(jì)

@echo off
VERIFY errors 2>nul
SETLOCAL ENABLEDELAYEDEXPANSION 
SETLOCAL ENABLEEXTENSIONS
Set outcome=did our best
if ERRORLEVEL 1 (
   echo could not set local execution environment
   goto bombsite
   )
Rem set Source to the database you wish to take a snapshot of
Set SourceDatabase=MyDatabase
Rem Set SourceServer to the name of the server or instance containing this database
Set SourceServer=MyServerOrInstance
Rem Specify where you would like to save the snapshot file
Set SnapShotPath="%HOMEDRIVE%%HOMEPATH%\documents\%SourceDatabase%.snp"
Rem We now to check if you have provided credentials
REM before you start. You need to write out your SQLCMD credentials to a file in your user
rem 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 credentials
Rem echo MyUserID/MyPassword>%userProfile%\%SourceServer:\=_%SQLSnapper.txt 
Rem if ERRORLEVEL 1 (
Rem   echo Could not write Source Credentials
REM   goto bombsite
REM   )
rem
REM read in your Source SQLCMD command and credentials if you have any
if exist %userProfile%\%SourceServer:\=_%SQLSnapper.txt (
   Set /p SourceCredentials=<%userProfile%\%SourceServer:\=_%SQLSnapper.txt 
   
if ERRORLEVEL 1 (
  echo Could not read in Source Credentials
  goto bombsite
)
)
Rem Parse the source credentials into two variables
set "Sourceuid=%SourceCredentials:/=" & set "SourcePw=%"
REM credentials are presented in two different ways by the CLI apps
Set SourcesqlcmdCredentials= /U %Sourceuid% /P %SourcePw%
Set SourceSQLSnapperCredentials= /username:%Sourceuid% /Password:%SourcePw%
 
REM Set source credentials correctly for windows security
IF NOT DEFINED  SourceCredentials ( 
  Set SourcesqlcmdCredentials =
  set SourceSQLSnapperCredentials =
 )
 echo Creating Snapshot from database %SourceDatabase% on %sourceServer%  as %Source
   "%ProgramFiles(x86)%\\Red Gate\SQL Compare 14\Redgate.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 
:bombsite
REM This is where the program goes if a problem is detected
color 04
Echo Sadly, we failed, though we %outcome%!
echo on
ENDLOCAL
color
Exit /b 1
:end
REM This is where we go if all ended well. 
Echo we %outcome% and finished successfully
ENDLOCAL
echo on
Exit /b 0

未完待續(xù)……下一章將繼續(xù)講解