SQL Compare是一款比較和同步SQL Server數據庫結構的工具?，F有超過150,000的數據庫管理員、開發人員和測試人員在使用它。當測試本地數據庫，暫存或激活遠程服務器的數據庫時，SQL Compare將分配數據庫的過程自動化。

點擊下載SQL Compare試用版

假設您需要構建數據庫的最新版本，將其存儲有測試所需的數據，然后分發該數據庫的多個副本。到目前為止，最快，最簡單的方法就是使用SQL Clone，尤其是在數據庫很大的情況下?；蛘?，使用SQL Toolbelt，則可以從源目錄構建五個副本中的每個副本，通過BCP將它們填充到標準測試數據集，然后使用SQL Change Automation和某些PowerShell（或SQL Compare CLI和PowerShell）來部署它們。

但是，顯然，仍然有Ops的人更喜歡使用PowerShell而非DOS腳本，因為我最近關于該主題的博客之一令人驚訝地受歡迎。他們熟悉它，并且經常仍然維護著現有DOS腳本的大量存檔。我沉思，可以創建一個執行相同功能的DOS批處理腳本嗎？沒有PowerShell，沒有SMO，只有SQL Compare，SQLCMD和BCP。

DOS批處理腳本

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

除了給管理員帶來溫暖，懷舊的感覺外，有時還存在共享數據庫租用之類的時間，或者創建帶有特殊數據集進行測試的數據庫時，像這樣基于DOS的方法會更方便。

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

建立和填充數據庫的任務

在開發和測試期間，您通常需要創建一個或多個數據庫版本的副本，并存儲測試所需的特定數據。您可能需要維護幾個數據集。例如：

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

運行代碼

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

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

運行這個非常簡單。您做什么取決于任務。當然，您可以為每個任務設置批處理文件的不同版本，也可以選擇較低級別的維護選項，以記住源中注釋的配置選項。

1.從源目錄創建沒有數據的目標數據庫

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

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

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

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

和以前一樣，在腳本的開頭填寫源數據庫和服務器的名稱，目標數據庫和服務器的名稱以及工作路徑位置。將輸出設置為no并將輸入設置為no。

除非您要使用Windows登錄名進行操作，否則還需要將SQL Server憑據寫入一個文件，正如我在源代碼中所指出的那樣，該文件用于在用戶區域根目錄中使用的每臺服務器。這僅必須執行一次，然后您應該刪除代碼！我提供了執行此操作的源代碼：

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

運行DOS批處理腳本

打開命令提示符，然后鍵入批處理文件的名稱（包括路徑），然后關閉。DOS文件的性質就是這樣，事情很容易出錯，但是這段代碼應該不會有太多問題。要進行調試，請先刪除@echo第一行的內容，以便查看批處理的運行方式。這里還有更多提示。
這是典型的輸出，執行剛剛完成：

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

結論

可以在DOS下作為CLI應用程序運行的應用程序的樂趣在于，您可以從多種腳本語言和方法中進行選擇。盡管我喜歡PowerShell，但周圍有很多人，特別是在Ops中，他們實際上更喜歡DOS批處理語言，因為它離操作系統很近，它在現有腳本的大型庫中使用，并且無需運行即可運行。特殊的腳本環境。

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

相關產品推薦：

SQL Prompt：SQL語法提示工具

SQL Toolbelt：Red Gate產品套包

SQL Monitor：SQL Server監控工具