SQL Compare使用教程:如何使用SQL Compare CLI和批处理脚本构建和填充SQL Server数据库

翻译|使用教程|编辑:杨鹏连|2020-08-06 10:04:03.290|阅读 17 次

概述:Phil Factor提供了功能强大的DOS批处理脚本,当与SQL Compare CLI结合使用时,您可以在开发过程中从源构建数据库,并用测试所需的特定数据集填充它们。

# 正版采购狂欢节,企业采购正当时 #

SQL Compare是一款比较和同步SQL Server数据库结构的工具。现有超过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上非常流行,但它与我们所使用的通用脚本语言非常接近。

建立和填充数据库的任务

在开发和测试期间,您通常需要创建一个或多个数据库版本的副本,并存储测试所需的特定数据。您可能需要维护几个数据集。例如:

  • 可能不允许您访问真实数据。这样做有很多充分的理由。它可以是私人的也可以是机密的。这可能是非法的。因此,您将需要一个生成的数据集,该数据集在本质上与实数据尽可能接近,或者是真实数据的蒙版匿名版本。
  • 您可能正在支持一系列需要标准化数据集的测试,每次测试都需要使用标准化数据集,以使测试人员能够进行黑盒测试。
  • 您可能正在进行可伸缩性测试,以确保数据库设计在处理不同数量的数据时性能良好。
我们需要一种自动的方法来构建每个工作数据库副本。如前所述,虽然有更简便,更快捷的方法,但是我们将使用DOS批处理文件。它将使用SQL Compare从源目录构建数据库,然后使用BCP文件现有目录中的数据填充该数据库。为此,您提供根目录的名称,并将其存储在代表服务器和数据库的子目录下的数据。它根据需要创建这些目录。DOS脚本联系新建的数据库,获取表列表,然后将数据从源目录到目标进行BCP。
创建初始工作副本后,我们可以将其用作源数据库来创建后续副本。在创建空的目标数据库之前,DOS批处理脚本将检查目标并删除任何同名的现有数据库。然后,它使用SQL Compare将空目标的模式与源同步,然后最终复制源数据库中的所有数据。
除许多看似简单的任务外,还存在某些复杂性。目标服务器需要使用正确的扩展名进行适当的设置,例如全文搜索,以满足数据库的要求。数据库的要求可能需要特殊的过滤器或开关。使用SQL Compare,我们可以使用ArgFiles来优化脚本以允许这样做。我展示了一种滑入BCP中可配置参数的方法,因为我需要具有这样的设备才能进行ID /密码验证。
如果表或数据库中包含非法名称且其中带有空格,则必须加以处理。如果在存储文件时将它们用作目录名,则SQL Server实例名称也需要修改。
一项比较棘手的任务是从磁盘读取任何必需的ID和密码。将它们保存在脚本中是可怕的。至少必须将它们保存在用户区的文件中,在该文件中NTFS安全性应使它们不被窥视。在理想情况下,每个应用程序都将接受并读取标准连接字符串,但可悲的是,每个CLI应用程序似乎都有不同的方式来执行此操作,因此必须对凭据进行解析并为命令行正确布局。

运行代码

出于本文的目的,该脚本采用了意识流样式,以使其易于遵循,尽管如果您要做的工作很多,可以很容易地将其变成一个函数。

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(意味着不要将数据从源复制或复制到目标)。

2.从源目录创建目标数据库,并从BCP目录填充数据

在脚本的开头,在变量SourceScriptsDirectory中填写源目录的路径,将目标数据库的名称添加到Target并将目标服务器的名称添加到TargetServer。指定要在workpath文件位置,路径到包含原始数据BCP文件的目录。将输出设置为no并将输入设置为yes(表示不从源复制数据,而是将其复制到目标)

图片包含文字,地图说明自动生成
3.从源目录创建目标数据库,并填充源数据库中的数据

在脚本的开头,在变量SourceScriptsDirectory中填写源目录的路径,将目标数据库的名称添加到Target并将目标服务器的名称添加到TargetServer。指定要在workpath文件位置,路径到包含原始数据BCP文件的目录。将输出设置为yes,将输入设置为yes(表示从源复制数据并将其复制到目标)。

图片包含文字,地图说明自动生成
4.将目标数据库与源数据库(数据中的BCP)同步

您只需要在脚本的开头填写源数据库和服务器的名称,目标数据库和服务器的名称以及放置本地BCP数据文件的工作路径文件位置。将输出设置为yes并将输入设置为yes。

自动生成的地图说明的特写

5.在没有数据的情况下将目标数据库与源数据库同步

和以前一样,在脚本的开头填写源数据库和服务器的名称,目标数据库和服务器的名称以及工作路径位置。将输出设置为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
只需删除REM关键字,添加您的UserID和凭据,一切都应该很好。执行代码后,不要忘记删除代码。

运行DOS批处理脚本

打开命令提示符,然后键入批处理文件的名称(包括路径),然后关闭。DOS文件的性质就是这样,事情很容易出错,但是这段代码应该不会有太多问题。要进行调试,请先删除@echo第一行的内容,以便查看批处理的运行方式。这里还有更多提示。
这是典型的输出,执行刚刚完成:

运行脚本后,您已经包括了BCP OUT操作(output=true),您应该在此处的目录中看到文件:

如果启动SQL数据比较,它将告诉您数据库的两个副本中的数据都相同。SQL Server也会对元数据说同样的话。

结论

可以在DOS下作为CLI应用程序运行的应用程序的乐趣在于,您可以从多种脚本语言和方法中进行选择。尽管我喜欢PowerShell,但周围有很多人,特别是在Ops中,他们实际上更喜欢DOS批处理语言,因为它离操作系统很近,它在现有脚本的大型库中使用,并且无需运行即可运行。特殊的脚本环境。

SQLCMD是一个功能强大的系统,您可以轻松地用其他方法来做很多事情。甚至数据库开发人员也可以在常用片段库的帮助下,将DOS视为吊装脚本的便捷方法。如果将此功能与具有CLI接口的数据库工具(例如SQL比较,SQL数据比较或SQL数据生成器)结合使用,那么您将拥有一个功能强大的工具包,可用于创建最新的开发版本并用所需的特定数据集填充它们。

相关产品推荐:

SQL Prompt:SQL语法提示工具

SQL Toolbelt:Red Gate产品套包

SQL Monitor:SQL Server监控工具


想要购买SQL Compare正版授权,或了解更多产品信息请点击【咨询在线客服】



标签:

本站文章除注明转载外,均为本站原创或翻译。欢迎任何形式的转载,但请务必注明出处、不得修改原文相关链接,尊重他人劳动成果

文章转载自:Red Gate

登录 慧都网发表评论


暂无评论...

为你推荐

  • 推荐视频
  • 推荐活动
  • 推荐产品
  • 推荐文章
  • 慧都慧问
相关厂商
相关产品
SQL Prompt

SQL Prompt是一款实用的SQL语法提示工具。

在线
客服
咨询
电话
400-700-1020
在线
QQ
购物车 反馈 返回
顶部
在线客服系统
live chat