彩票走势图

SQL Compar是一款比较和同步SQL Server数据库结构的工具。现有超过150,000的数据库管理员、开发人员和测试人员在使用它。当测试本地数据库，暂存或激活远程服务器的数据库时，SQL Compare将分配数据库的过程自动化。

下载SQL Compar

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

如何分发SQL Snapper？

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

自动化SQL快照程序入门

从PowerShell运行snapper并不难。基本上，如果您很幸运并且对服务器具有Windows身份验证，并且不必太担心理解错误，则可以执行此操作（实际别名取决于您当前的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" }

生成单个数据库的快照

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

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
    
}

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

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

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

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

@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

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