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

點(diǎn)擊下載SQL Compare試用版

假設(shè)您需要構(gòu)建數(shù)據(jù)庫(kù)的最新版本，將其存儲(chǔ)有測(cè)試所需的數(shù)據(jù)，然后分發(fā)該數(shù)據(jù)庫(kù)的多個(gè)副本。到目前為止，最快，最簡(jiǎn)單的方法就是使用SQL Clone，尤其是在數(shù)據(jù)庫(kù)很大的情況下?；蛘撸褂肧QL Toolbelt，則可以從源目錄構(gòu)建五個(gè)副本中的每個(gè)副本，通過BCP將它們填充到標(biāo)準(zhǔn)測(cè)試數(shù)據(jù)集，然后使用SQL Change Automation和某些PowerShell（或SQL Compare CLI和PowerShell）來部署它們。

但是，顯然，仍然有Ops的人更喜歡使用PowerShell而非DOS腳本，因?yàn)槲易罱P(guān)于該主題的博客之一令人驚訝地受歡迎。他們熟悉它，并且經(jīng)常仍然維護(hù)著現(xiàn)有DOS腳本的大量存檔。我沉思，可以創(chuàng)建一個(gè)執(zhí)行相同功能的DOS批處理腳本嗎？沒有PowerShell，沒有SMO，只有SQL Compare，SQLCMD和BCP。

DOS批處理腳本

乍一看，很難想象沒有一種比DOS Batch更有前景的腳本語言，盡管它特別適合于文件系統(tǒng)任務(wù)。它的根源可以通過CP / M和MSDOS追溯到UNIX。它仍在MSDOS中。這些年來，我不得不多次使用它來執(zhí)行沒有現(xiàn)實(shí)選擇的任務(wù)。令人驚訝的是，它仍然吸引了少量但專注的追隨者。有關(guān)示例的最新和有趣的集合，請(qǐng)參見“ Rosetta代碼：Category：Batch文件”和DosTips – DOS 批處理指南。Rosetta Code網(wǎng)站允許您比較不同語言執(zhí)行標(biāo)準(zhǔn)算法和任務(wù)時(shí)的代碼。

除了給管理員帶來溫暖，懷舊的感覺外，有時(shí)還存在共享數(shù)據(jù)庫(kù)租用之類的時(shí)間，或者創(chuàng)建帶有特殊數(shù)據(jù)集進(jìn)行測(cè)試的數(shù)據(jù)庫(kù)時(shí)，像這樣基于DOS的方法會(huì)更方便。

更重要的是，幾乎每個(gè)版本管理工具，構(gòu)建自動(dòng)化工具，部署工作流系統(tǒng)，配置管理或持續(xù)集成工具都允許您執(zhí)行DOS批處理。盡管PowerShell在Windows上非常流行，但它與我們所使用的通用腳本語言非常接近。

建立和填充數(shù)據(jù)庫(kù)的任務(wù)

在開發(fā)和測(cè)試期間，您通常需要?jiǎng)?chuàng)建一個(gè)或多個(gè)數(shù)據(jù)庫(kù)版本的副本，并存儲(chǔ)測(cè)試所需的特定數(shù)據(jù)。您可能需要維護(hù)幾個(gè)數(shù)據(jù)集。例如：

• 可能不允許您訪問真實(shí)數(shù)據(jù)。這樣做有很多充分的理由。它可以是私人的也可以是機(jī)密的。這可能是非法的。因此，您將需要一個(gè)生成的數(shù)據(jù)集，該數(shù)據(jù)集在本質(zhì)上與實(shí)數(shù)據(jù)盡可能接近，或者是真實(shí)數(shù)據(jù)的蒙版匿名版本。
• 您可能正在支持一系列需要標(biāo)準(zhǔn)化數(shù)據(jù)集的測(cè)試，每次測(cè)試都需要使用標(biāo)準(zhǔn)化數(shù)據(jù)集，以使測(cè)試人員能夠進(jìn)行黑盒測(cè)試。
• 您可能正在進(jìn)行可伸縮性測(cè)試，以確保數(shù)據(jù)庫(kù)設(shè)計(jì)在處理不同數(shù)量的數(shù)據(jù)時(shí)性能良好。

運(yùn)行代碼

出于本文的目的，該腳本采用了意識(shí)流樣式，以使其易于遵循，盡管如果您要做的工作很多，可以很容易地將其變成一個(gè)函數(shù)。

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 output to yes or no depending on whether you want the source to have its data copied out
Set output=yes 
REM set input to yes or no depending on whether you want the target to have its data copied in
Set input=yes
Rem set Source to the database you wish to copy
Set Source=MySourceDatabase
Rem Set SourceServer to the name of the server or instance to copy from
Set Sourceserver=MySourceServer
Rem set Target to the database you wish to copy to
Set Target=TestCopy
Rem BEWARE!! It deletes the existing copy of the database
Rem Set TargetServer to the name of the server or instance to copy to
Set TargetServer=MyDestinationServer
REM Specify your work directory. I chose 'BatchBCP' in my user area
Set workpath=%userProfile%\BatchBCP
Rem Specify a scripts directory for the source if you want one. otherwise put 'none'
Set SourceScriptsDirectory=c:\MySQLSourceDirectories\MyDatabase
REM before you start, if 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 echo MyUserID/MyPassword>%userProfile%\%TargetServer:\=_%SQLCompare.txt 
rem echo MyOtherUserID/MyOtherPassword>%userProfile%\%SourceServer:\=_%SQLCompare.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:\=_%SQLCompare.txt (
   Set /p SourceCredentials=<%userProfile%\%SourceServer:\=_%SQLCompare.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 SourceSQLCompareCredentials= /username1:%Sourceuid% /Password1:%SourcePw%
rem set source credentials correctly for windows security
IF NOT DEFINED  SourceCredentials ( 
  Set SourcesqlcmdCredentials =
  set SourceSQLCompareCredentials =
 )
REM read in your Target SQLCMD command and credentials if you have any
if exist %userProfile%\%TargetServer:\=_%SQLCompare.txt (
Set /p TargetCredentials=<%userProfile%\%TargetServer:\=_%SQLCompare.txt
)
if ERRORLEVEL 1 (
      echo Could not read in Target Credentials
      goto bombsite
    )
Rem Parse the target credentials into two variables
set "Targetuid=%TargetCredentials:/=" & set "TargetPw=%"
Rem credentials are presented in two different ways by the CLI apps
Set TargetsqlcmdCredentials= /U %Targetuid% /P %TargetPw%
Set TargetSQLCompareCredentials= /username2:%Targetuid% /Password2:%TargetPw%
rem set target credentials orrectly for windows security
IF NOT DEFINED  TargetCredentials ( 
  Set TargetsqlcmdCredentials =
  set TargetSQLCompareCredentials =
 )
Rem Now we check the Target database on the target server to see if it
Rem already exists, If so, we delete it and create an empty database
Set QUERY= IF EXISTS (SELECT name FROM sys.databases  where NAME LIKE 'testcopy' ) DROP DATABASE TestCopy; CREATE DATABASE TestCopy
sqlcmd -S %targetServer% %TargetsqlcmdCredentials% -d master -h -1  -f 65001  -Q "%QUERY%"
if ERRORLEVEL 1 (
   echo Failed to use target %targetServer% to create %target%.
   goto bombsite
   )
Rem now we synchronize the source with the target to provide  a fresh 
Rem new database at the right level
if %SourceScriptsDirectory% == none (
   echo synchronizing database %Source% on %sourceServer%  with %Target% on %TargetServer%
   "%ProgramFiles(x86)%\Red Gate\SQL Compare 13\sqlcompare.exe" /server1:%sourceServer% /database1:%Source%  %SourceSQLCompareCredentials% /server2:%targetServer% /database2:%Target% %TargetSQLCompareCredentials% /Synchronize
   SET outcome=created database %Target% on %TargetServer% from  %Source% on %sourceServer%
   ) else (
   echo synchronizing scripts directory %SourceScriptsDirectory% with %Target% on %TargetServer%
   "%ProgramFiles(x86)%\Red Gate\SQL Compare 13\sqlcompare.exe" /scripts1:%SourceScriptsDirectory% /server2:%targetServer% /database2:%Target% %TargetSQLCompareCredentials% /Synchronize
   SET outcome=created database %Target% on %TargetServer% from scripts
   )
if ERRORLEVEL 1 (
   echo An error with SQL Compare occurred.
   goto bombsite
   )
REM see the output onscreen while debugging.
REM check whether the database directory within the workpath/server directory exists
if not exist "%workpath%\%SourceServer:\=_%\%Source%" (md %workpath%\%SourceServer:\=_%\%Source%) 
if ERRORLEVEL 1 (
   echo An error creating "%workpath%\%SourceServer:\=_%\%Source%"  for database occurred 
   goto bombsite
   )
Echo Copying table data from %SourceServer%.%Source% to "%workpath%\%SourceServer:\=_%\%Source% "
Rem Create the query that brings you the list of tables. This is used for both 
rem the input and output operations. We can cope with tables that use  illegal characters
Set QUERY="SET NOCOUNT ON; SELECT replace(Quotename(Object_Schema_Name(object_id))+'.'+Quotename(name),' ','--') AS The_Tables FROM sys.tables WHERE is_ms_shipped=0;"
Rem only do the next block if the user wants data copied out from the source
if %output% == yes (
   REM Execute the query and work through the returned list of tables
   for /F usebackq %%i in (`sqlcmd -S %sourceServer%  %SourcesqlcmdCredentials%  -d %Source% -h -1  -f 65001  -Q %QUERY%`) do (
      rem for every tablespec in the list append the following text ....
      REM catch the first error caused by the sqlcmd 
      if ERRORLEVEL 1 (
         echo An error ovccured while accessing  %SourceServer%  to get the list of tables
         goto bombsite
      )
      Set Tablename= %%i
      for /f "tokens=* delims= " %%a in ("!Tablename!") do set Tablename=%%a
      Rem correct the name of the table
      Set Tablename=!Tablename:--= !
      REM change a dot for a dash as it isn't legal
      set filename= !Tablename:.=-!
      REM trim the filename- well, remove all spaces
      Set "filename=!filename: =!"
      echo Copying out data from !Tablename! in database %Source% on server %SourceServer% to !filename!.bcp
      BCP "!Tablename!" out  %workpath%\%SourceServer:\=_%\%Source%\!filename!.bcp  -n -d %Source%  -S %sourceServer%   %SourcesqlcmdCredentials% 
      if ERRORLEVEL 1 (
         echo BCP error when copying out data from !Tablename! in database %Source% on server %SourceServer%
         goto bombsite
         )
      ) 
      SET outcome=%outcome%, copied out data from %Source% on %SourceServer%
   )
Rem only do the next block if the user wants data copied out to the target
if %Input% == yes (
   Echo Copying table data to %TargetServer%.%Target% from "%workpath%\%SourceServer:\=_%\%Source%"
   REM Execute the query and create the entire SQL Command file that will be executed
   for /F usebackq %%i in (`sqlcmd -S %TargetServer%   %TargetsqlcmdCredentials%  -d %Target% -h -1  -f 65001  -Q %QUERY%`) do (
      rem for every tablespec in the list append the following text ....
      REM catch the first error caused by the sqlcmd 
      if ERRORLEVEL 1 (
         echo An error occured while accessing  %TargetServer%  to get the list of tables
         goto bombsite
         )
      Set Tablename= %%i
      for /f "tokens=* delims= " %%a in ("!Tablename!") do set Tablename=%%a
      Rem correct the name of the table
      Set Tablename=!Tablename:--= !
      REM change a dot for a dash as it isn't legal
      set filename= !Tablename:.=-!
      REM trim the filename- well, remove all spaces
      Set "filename=!filename: =!"
      echo Copying in data to !Tablename! in database %Target% on server %TargetServer% from %workpath%\%SourceServer:\=_%\%Source%\!filename!.bcp
      BCP "!Tablename!" in  %workpath%\%SourceServer:\=_%\%Source%\!filename!.bcp -n -d %Target% -E -S %TargetServer%   %TargetsqlcmdCredentials% 
      if ERRORLEVEL 1 (
         echo BCP error when copying in data from !Tablename! in database %target% on server %TargetServer%
         goto bombsite
         )
      ) 
   SET outcome=%outcome%, copied out data from %Source% on %SourceServer%
   )
 
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

運(yùn)行這個(gè)非常簡(jiǎn)單。您做什么取決于任務(wù)。當(dāng)然，您可以為每個(gè)任務(wù)設(shè)置批處理文件的不同版本，也可以選擇較低級(jí)別的維護(hù)選項(xiàng)，以記住源中注釋的配置選項(xiàng)。

1.從源目錄創(chuàng)建沒有數(shù)據(jù)的目標(biāo)數(shù)據(jù)庫(kù)

在腳本的開始，在路徑的源目錄作為變量的值填充SourceScriptsDirectory，添加目標(biāo)數(shù)據(jù)庫(kù)的名稱目標(biāo)和實(shí)現(xiàn)目標(biāo)服務(wù)器TargetServer，并在該指定workpath文件位置的路徑包含本地BCP數(shù)據(jù)文件的目錄。將輸出設(shè)置為no，將輸入設(shè)置為no（意味著不要將數(shù)據(jù)從源復(fù)制或復(fù)制到目標(biāo)）。

在腳本的開頭，在變量SourceScriptsDirectory中填寫源目錄的路徑，將目標(biāo)數(shù)據(jù)庫(kù)的名稱添加到Target并將目標(biāo)服務(wù)器的名稱添加到TargetServer。指定要在workpath文件位置，路徑到包含原始數(shù)據(jù)BCP文件的目錄。將輸出設(shè)置為no并將輸入設(shè)置為yes（表示不從源復(fù)制數(shù)據(jù)，而是將其復(fù)制到目標(biāo)）

在腳本的開頭，在變量SourceScriptsDirectory中填寫源目錄的路徑，將目標(biāo)數(shù)據(jù)庫(kù)的名稱添加到Target并將目標(biāo)服務(wù)器的名稱添加到TargetServer。指定要在workpath文件位置，路徑到包含原始數(shù)據(jù)BCP文件的目錄。將輸出設(shè)置為yes，將輸入設(shè)置為yes（表示從源復(fù)制數(shù)據(jù)并將其復(fù)制到目標(biāo)）。

您只需要在腳本的開頭填寫源數(shù)據(jù)庫(kù)和服務(wù)器的名稱，目標(biāo)數(shù)據(jù)庫(kù)和服務(wù)器的名稱以及放置本地BCP數(shù)據(jù)文件的工作路徑文件位置。將輸出設(shè)置為yes并將輸入設(shè)置為yes。

和以前一樣，在腳本的開頭填寫源數(shù)據(jù)庫(kù)和服務(wù)器的名稱，目標(biāo)數(shù)據(jù)庫(kù)和服務(wù)器的名稱以及工作路徑位置。將輸出設(shè)置為no并將輸入設(shè)置為no。

除非您要使用Windows登錄名進(jìn)行操作，否則還需要將SQL Server憑據(jù)寫入一個(gè)文件，正如我在源代碼中所指出的那樣，該文件用于在用戶區(qū)域根目錄中使用的每臺(tái)服務(wù)器。這僅必須執(zhí)行一次，然后您應(yīng)該刪除代碼！我提供了執(zhí)行此操作的源代碼：

REM before you start, if 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 echo MyUserID/MyPassword>%userProfile%\%TargetServer:\=_%SQLCompare.txt 
rem echo MyOtherUserID/MyOtherPassword>%userProfile%\%SourceServer:\=_%SQLCompare.txt 
Rem if ERRORLEVEL 1 (
Rem   echo Could not write Source Credentials
REM   goto bombsite
REM   )
rem

運(yùn)行DOS批處理腳本

打開命令提示符，然后鍵入批處理文件的名稱（包括路徑），然后關(guān)閉。DOS文件的性質(zhì)就是這樣，事情很容易出錯(cuò)，但是這段代碼應(yīng)該不會(huì)有太多問題。要進(jìn)行調(diào)試，請(qǐng)先刪除@echo第一行的內(nèi)容，以便查看批處理的運(yùn)行方式。這里還有更多提示。
這是典型的輸出，執(zhí)行剛剛完成：

運(yùn)行腳本后，您已經(jīng)包括了BCP OUT操作（output=true），您應(yīng)該在此處的目錄中看到文件：

結(jié)論

可以在DOS下作為CLI應(yīng)用程序運(yùn)行的應(yīng)用程序的樂趣在于，您可以從多種腳本語言和方法中進(jìn)行選擇。盡管我喜歡PowerShell，但周圍有很多人，特別是在Ops中，他們實(shí)際上更喜歡DOS批處理語言，因?yàn)樗x操作系統(tǒng)很近，它在現(xiàn)有腳本的大型庫(kù)中使用，并且無需運(yùn)行即可運(yùn)行。特殊的腳本環(huán)境。

SQLCMD是一個(gè)功能強(qiáng)大的系統(tǒng)，您可以輕松地用其他方法來做很多事情。甚至數(shù)據(jù)庫(kù)開發(fā)人員也可以在常用片段庫(kù)的幫助下，將DOS視為吊裝腳本的便捷方法。如果將此功能與具有CLI接口的數(shù)據(jù)庫(kù)工具（例如SQL比較，SQL數(shù)據(jù)比較或SQL數(shù)據(jù)生成器）結(jié)合使用，那么您將擁有一個(gè)功能強(qiáng)大的工具包，可用于創(chuàng)建最新的開發(fā)版本并用所需的特定數(shù)據(jù)集填充它們。

相關(guān)產(chǎn)品推薦：

SQL Prompt：SQL語法提示工具

SQL Toolbelt：Red Gate產(chǎn)品套包

SQL Monitor：SQL Server監(jiān)控工具