本文介绍了如何使用SqlPackage.exe工具和PowerShell脚本来自动化SQL Server数据库的刷新过程。通过创建和应用bacpac文件，数据库管理员可以更高效地执行数据库备份和还原任务，特别是在持续集成和持续部署的场景中。文章详细讨论了如何准备PowerShell脚本，设置环境变量，以及使用SqlPackage工具执行导出和导入操作。
Data is the key to your organization’s future, but if it’s outdated, irrelevant, or hidden then it’s no good. Maintenance and administration of databases takes a lot of work. As database administrators, we often tend to automate most of these repetitive tasks. A database refresh is one of the most common tasks performed by most of the administrators as part of their daily routine.
数据是组织未来的关键，但是，如果数据过时，不相关或隐藏，那就不好了。 数据库的维护和管理需要大量工作。 作为数据库管理员，我们通常倾向于自动执行大多数重复性任务。 数据库刷新是大多数管理员在日常工作中执行的最常见任务之一。
Today, database refreshes are quite frequent because of Continuous Integration (CI) and Continuous Deployment (CD). In most of the cases, testing requires a separate but current production dataset to ensure the validity of the desired result.
如今，由于持续集成（CI）和持续部署（CD），数据库刷新非常频繁。 在大多数情况下，测试需要一个单独的但最新的生产数据集以确保所需结果的有效性。
In today’s world, we rely more on third party tools to perform a Backup and Restore of databases. With many advanced tools and techniques, this is a pretty straight forward approach. Think of the real-world scenarios where customers rely on the native SQL Tools and techniques. Creating an automated database refresh tasks regularly will have a huge impact on the quality of the release management cycles and would save a lot of time for the database administrators.
在当今世界，我们更多地依赖第三方工具来执行数据库的备份和还原。 使用许多先进的工具和技术，这是一种非常简单的方法。 想一想客户依赖本机SQL工具和技术的实际情况。 定期创建自动的数据库刷新任务将对发布管理周期的质量产生巨大影响，并为数据库管理员节省大量时间。
There are many ways to automate this, some of which are:
有许多方法可以使它自动化，其中包括：
- SQLCMD SQLCMD
- PowerShell 电源外壳
- SqlPackage SqlPackage
In this article, we about the following:
在本文中，我们讨论以下内容：
- Details of Sqlcmd Sqlcmd的详细信息
- The use of the cross-platform tool, Sqlpackage 跨平台工具Sqlpackage的使用
- Automation using Windows batch scripting 使用Windows批处理脚本进行自动化
- And more… 和更多…
Using sqlcmd provides flexible ways to execute T-SQLs and SQL script files. As its available on Linux, Windows and Mac, this command line utility plays a vital role in managing the database restore operations in a DevOps pipeline.
使用sqlcmd提供了执行T-SQL和SQL脚本文件的灵活方法。 该命令行实用程序可在Linux，Windows和Mac上使用，在管理DevOps管道中的数据库还原操作方面起着至关重要的作用。
PowerShell脚本使用SqlPackage.exe自动创建bacpac文件并使用创建的bacpac还原数据库 (PowerShell script to automatically create a bacpac file and restore the database using the created bacpac, using SqlPackage.exe)
This section deals with the preparation of a PowerShell script to automate database restoration using the SqlPackage tool which is part of the SQL Server Data Tools suite.
本节介绍如何使用SqlPackage工具（它是SQL Server数据工具套件的一部分）来准备PowerShell脚本以自动执行数据库还原。
The first step is to prepare and set the environment variables. The SqlPackage tool is installed under C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin. The script uses sqlcmd and SqlPackage tool; make sure that the path variable is updated accordingly.
第一步是准备和设置环境变量。 SqlPackage工具安装在C：\ Program Files（x86）\ Microsoft SQL Server \ 140 \ DAC \ bin下。 该脚本使用sqlcmd和SqlPackage工具； 确保相应地更新路径变量。
- The input parameter section lists the source, target SQL databases instance and folder for extracting the bacpac file 输入参数部分列出了用于提取bacpac文件的源，目标SQL数据库实例和文件夹
- Create the bacpac file using export action type 使用导出操作类型创建bacpac文件
- Select the latest bacpac file for further database restoration action 选择最新的bacpac文件以执行进一步的数据库还原操作
- Drop the destination database using the sqlcmd command 使用sqlcmd命令删除目标数据库
- Restore he database using import action type. 使用导入操作类型还原数据库。
First, let’s work on the getting and setting the environment variables. Pull the details of the environment variables into a local variable. The output shows if the environment variable contains the path or not (if not, the path is added to the PATH variable).
首先，让我们开始获取和设置环境变量。 将环境变量的详细信息拉入局部变量。 输出显示环境变量是否包含路径（如果不包含，则将路径添加到PATH变量）。
$PathVariables=$env:Path
$PathVariables
IF (-not $PathVariables.Contains( "C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin"))
{
write-host "SQLPackage.exe path is not found, Update the environment variable"
$env:Path = $env:Path + ";C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin;"
}
Defining the required input parameters
定义所需的输入参数
- Backup directory 备份目录
- Source database name 源数据库名称
- Source server name 源服务器名称
- Target server name (in this case it’s a Linux machine) 目标服务器名称（在本例中为Linux计算机）
$BackupDirectory="c:\SQLShackDemo\"
$DatabaseName="ApexSQLBackup"
#Source SQL Server name
$SourceServerName="HQDBT01"
#target instance is SQL Server on Linux Machine
$TargetserverName="10.2.6.51"
准备目标文件名 (Prepare the target filename)
In most of the case, defining a backup file is a standard practice as one backup file may not be enough to safeguard the entire system. So, a backup file name should be more meaningful with timestamps attached to it, and it should speak a little about the data. It includes the following;
在大多数情况下，定义备份文件是一种标准做法，因为一个备份文件可能不足以保护整个系统。 因此，备份文件名应附加时间戳，使其更有意义，并且应略述数据。 它包括以下内容；
- Filename 文档名称
- Extension 延期
- Timestamp yyyyMMddhhmmss format 时间戳yyyyMMddhhmmss格式
$dirName = [io.path]::GetDirectoryName($BackupDirectory)
#set the filename, the database should be a part of the filename
$filename = "ApexSQLBackup"
#extension must be bacpac
$ext = "bacpac"
#target filepath is a combination of the directory and filename appended with year month and day hour minutes and seconds
$TargetFilePath = "$dirName\$filename-$(get-date -f yyyyMMddhhmmss).$ext"
#print the full path of the target file path
$TargetFilePath
运行SqlPackage工具 (Run the SqlPackage tool)
Now, run SqlPackage.exe to export the ApexSQLBackup database. This step will create the BACPAC file for an existing database. The following sqlpackage.exe commands are used to export the database. These commands are compatible with all the databases on a local instance or remote instance of SQL Server, or even an Azure SQL Databases.
现在，运行SqlPackage.exe导出ApexSQLBackup数据库。 此步骤将为现有数据库创建BACPAC文件。 以下sqlpackage.exe命令用于导出数据库。 这些命令与SQL Server本地实例或远程实例上的所有数据库甚至Azure SQL数据库兼容。
sqlpackage.exe /a:Export /ssn:<SourceServerName> /sdn:<SourceDatabaseName> /su:<sourceUsername> /sp:<SourcePassword> /tf:<targetFileName>
sqlpackage.exe / a：导出/ ssn：<SourceServerName> / sdn：<SourceDatabaseName> / su：<sourceUsername> / sp：<SourcePassword> / tf：<targetFileName>
/Action – /a
/动作– / a
specify the action to be performed. In this case it’s Export, used to export the database into a bacpac file
指定要执行的动作。 在这种情况下，它是“导出”，用于将数据库导出到bacpac文件中
/SourceServerName -/ssn
/ SourceServerName-/ ssn
defines the name of the server hosting the database. It can be an Azure database URI or instance name or named instance
定义托管数据库的服务器的名称。 它可以是Azure数据库URI或实例名称或命名实例
/SourceDatabaseName (sdn)
/ SourceDatabaseName（sdn）
defines the name of the existing source database
定义现有源数据库的名称
/SourceUser (su)
/ SourceUser（su）
defines the SQL Server login username
定义SQL Server登录用户名
/SourcePassword (sp)
/ SourcePassword（sp）
defines the password; In this case, we’re exporting from a trusted SQL instance on Windows. For trusted connections, ignore the /su and /sp parameters
定义密码； 在这种情况下，我们将从Windows上的受信任SQL实例中导出。 对于可信连接，请忽略/ su和/ sp参数
In the following example, the short form is used
在下面的示例中，使用缩写形式
SqlPackage.exe /a:Export /ssn:$SourceServerName /sdn:$DatabaseName /tf:$TargetFilePath
Now, we’ll see how to get the latest bacpac file. In this case, selecting an item is done using Get-Childitem cmdlet. The specific files are listed using pattern matching. The output is then piped to get a sorted list in a descending order and then selected the first object of the list into the resultset.
现在，我们将了解如何获取最新的bacpac文件。 在这种情况下，使用Get-Childitem cmdlet完成选择项目。 使用模式匹配列出了特定文件。 然后将输出通过管道传递以获得降序排序的列表，然后将列表的第一个对象选择到结果集中。
$NewestBacPacFile = Get-ChildItem -Path $dirName\$filename*.$ext | Sort-Object LastAccessTime -Descending | Select-Object -First 1
$file="$NewestBacPacFile"
$file
There are some caveats to keep in mind while importing the data. The database must either not exist or be completely empty. The following SQL is used to check for the database existence on the target SQL instance. On its existence, the database is dropped using sqlcmd.
导入数据时需要注意一些注意事项。 数据库必须不存在或完全为空。 以下SQL用于检查目标SQL实例上是否存在数据库。 存在数据库后，将使用sqlcmd删除该数据库。
$DropSQL=
@"
IF EXISTS (SELECT name FROM sys.databases WHERE name = '$DatabaseName') DROP DATABASE $DatabaseName
"@
SQLCMD -S $TargetserverName -U SA -P thanVitha@2015 -Q $DropSQL
导入ApexSQLBackup数据库 (Import the ApexSQLBackup database)
To import the database, you can run the following command. These commands work for databases on a local instance or remote instance of SQL Server or even an Azure SQL Database instance:
要导入数据库，可以运行以下命令。 这些命令适用于SQL Server本地实例或远程实例甚至Azure SQL数据库实例上的数据库：
sqlpackage.exe /a:Import /sf:<SourcefileName> /tsn:<TargetServerName> /tdn:<targetdatabaseName> /su:<sourceUsername> /sp:<SourcePassword>
sqlpackage.exe / a：导入/ sf：<源文件名> / tsn：<目标服务器名> / tdn：<目标数据库名> / su：<源用户名> / sp：<源密码>
In this case, we are connecting to a Linux instance.
在这种情况下，我们正在连接到Linux实例。
Listed below are the arguments we’re using for importing the bacpac file. In my example above, I’m using the short form, you can use either long or short forms that suit your requirements or understanding
下面列出的是我们用于导入bacpac文件的参数。 在上面的示例中，我使用的是简写形式，您可以使用适合您的要求或理解的长或短形式
/Action – /a
/动作– / a
the action type, in this case its import, Importing database into Linux SQL instance
操作类型，在这种情况下为导入，将数据库导入Linux SQL实例
/SourceFileName -/sf
/ SourceFileName-/ sf
the path of the latest bacpac file of the database.
数据库的最新bacpac文件的路径。
/TargetDatabaseName -/sdn
/ TargetDatabaseName-/ sdn
the name of the target database
目标数据库的名称
/SourceUser (su)
/ SourceUser（su）
login user name
登录用户名
/SourcePassword (sp)
/ SourcePassword（sp）
the password; In this case its uses SQL authentication to connect to SQL on Linux. The /su and /sp parameters are in used in this place.
密码; 在这种情况下，它使用SQL身份验证连接到Linux上SQL。 在此位置使用了/ su和/ sp参数。
SqlPackage.exe /a:Import /sf:$file /tsn:$TargetserverName /tdn:$DatabaseName /tu:SA /tp:thanVitha@2015
输出量 (Output)
Copy and save the Appendix A content into sqlpackage.ps1 file
将附录A的内容复制并保存到sqlpackage.ps1文件中
自动化的Windows Batch脚本创建bacpac文件并使用创建的bacpac和SqlPackage.exe还原数据库 (Automated Windows Batch script to create bacpac file and restoring the database using the created bacpac using SqlPackage.exe)
A batch file is a collection of commands or executables, run within the command shell (CMD). It’s similar to a bash script in Unix/Linux.
批处理文件是在命令外壳（CMD）中运行的命令或可执行文件的集合。 它类似于Unix / Linux中的bash脚本。
The walkthrough of the batch file is pretty straight forward and it is no different from the PowerShell script that we just saw. I will discuss only the required pieces of the batch file in the next section.
批处理文件的演练非常简单，与我们刚刚看到的PowerShell脚本没有什么不同。 在下一节中，我将仅讨论批处理文件中所需的部分。
Let’s save the below following script code as a batch file. The filename has an extension of .bat or .cmd.
让我们将以下以下脚本代码保存为批处理文件。 文件名的扩展名为.bat或.cmd 。
I would like to take you through the for loop of the code. For example,
我想带您通过代码的for循环。 例如，
FOR /F [“options”] %%parameter IN (‘command to process’) DO command. Here are the steps we’d follow:
FOR / F [“选项”] %%参数IN（“要处理的命令”）DO命令。 这是我们要执行的步骤：
- Take a dataset 取得数据集
- Make a FOR Parameter %%G equal to some part of that data 使FOR参数%% G等于该数据的某些部分
- Execute a command (optionally using the parameters as part of the command). 执行命令（可以选择使用参数作为命令的一部分）。
- Repeat for each data item 对每个数据项重复
- If you are using the FOR command at the command line rather than in a batch program, use a single ‘%’ sign: %F 如果在命令行而不是在批处理程序中使用FOR命令，请使用单个'％'符号：％F
Save the batch script from Appendix B as a .bat file:
将附录B中的批处理脚本另存为.bat文件：
That’s it!
而已！
结语 (Wrapping up)
In this article, we looked at how a PowerShell or a Batch script can be used to automate data restoration using a bacpac file using sqlpackage.exe. This tool is one of the easiest tools that can be used to refresh the database. The code can be further simplified and automated for various other database refresh needs.
在本文中，我们研究了如何使用PowerShell或Batch脚本通过使用sqlpackage.exe的bacpac文件自动执行数据还原。 此工具是可用于刷新数据库的最简单工具之一。 可以针对各种其他数据库刷新需求进一步简化和自动化代码。
目录 (Table of contents)
参考资料 (References)
- SQL: How to refresh Multiple SQL Databases with PowerShell SQL：如何使用PowerShell刷新多个SQL数据库
- How to write a Batch File 如何编写批处理文件
- sqlcmd Utility sqlcmd实用程序
附录（A） (Appendix (A))
PowerShell script
PowerShell脚本
# Get the path variables details into the variable
$PathVariables=$env:Path
#Print the path variable
$PathVariables
#Check the path existence of the SqlPackage.exe and print its status
IF (-not $PathVariables.Contains( "C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin"))
{
write-host "SQLPackage.exe path is not found, Update the environment variable"
$env:Path = $env:Path + ";C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin;"
}
#Get the list and ensure the update is made on the environment variable
#$PathVariables=$env:Path
#print the variable data
#$PathVariables
#the input parameters
$BackupDirectory="c:\SQLShackDemo\"
$DatabaseName="ApexSQLBackup"
#Source server name is SQL Server
$SourceServerName="HQDBT01"
#target instance is SQL Server on Linux Machine
$TargetserverName="10.2.6.51"
#Prepare the targe filename
$dirName = [io.path]::GetDirectoryName($BackupDirectory)
#set the filename, the database should be a part of the filename
$filename = "ApexSQLBackup"
#extension must be bacpac
$ext = "bacpac"
#target filepath is a combination of the directory and filename appended with year month and day hour minutes and seconds
$TargetFilePath = "$dirName\$filename-$(get-date -f yyyyMMddhhmmss).$ext"
#print the full path of the target file path
$TargetFilePath
#Run the SqlPackage tool to export the database
SqlPackage.exe /a:Export /ssn:$SourceServerName /sdn:$DatabaseName /tf:$TargetFilePath
#Get the latest file in a given directory
$NewestBacPacFile = Get-ChildItem -Path $dirName\$filename*.$ext | Sort-Object LastAccessTime -Descending | Select-Object -First 1
#print the latest bacfile name depending the name of the database
$file="$NewestBacPacFile"
$FILE
#check or drop for the database existence on the target SQL instance
$DROPDATABASESQL=
@"
IF EXISTS (SELECT * FROM [sys].[databases] WHERE [name] = '$DatabaseName') DROP DATABASE $DatabaseName
"@
#Using sqlcmd, the dropdatabase sql is executed on the target instance.
SQLCMD -S $TargetserverName -U SA -P thanVitha@2015 -Q $DROPDATABASESQL
#Import the ApexSQLBackup database using sqlpackage.exe
SqlPackage.exe /a:Import /sf:$file /tsn:$TargetserverName /tdn:$DatabaseName /tu:SA /tp:thanVitha@2015
附录（B） (Appendix (B))
Batch script
批处理脚本
@echo OFF
SETLOCAL
REM The installation directory where SSDT tools
SET SQLDIR=C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin\
SET SQLPACKAGE="%SQLDIR%SqlPackage.exe"
REM Specify the path of the bacpac files
SET DATABASEDIR=c:\SQLShackDemo\
REM The database for export and import
SET DATABASENAME=ApexSQLBackup
REM The SQL Server Source instance
SET SOURCESERVERNAME=HQDBT01
REM The SQL Server target instance
SET TARGETSERVERNAME=10.2.6.51
REM Get the datetime in a format that can go in a filename.
For /f "tokens=2-4 delims=/ " %%a in ("%date%") do (set mydate=%%c-%%a-%%b)
For /f "tokens=1-2 delims=/:" %%a in ("%TIME%") do (set mytime=%%a%%b)
echo %mydate%_%mytime%
REM Export the database
%SQLPACKAGE% /a:Export /ssn:%SOURCESERVERNAME% /sdn:%DATABASENAME% /tf:%DATABASEDIR%%DATABASENAME%_%mydate%_%mytime%.bacpac
REM Drop the target database in order to make import command to work succesfully
SET DropSQL=IF EXISTS (SELECT name FROM sys.databases WHERE name = '%DATABASENAME%') DROP DATABASE [%DATABASENAME%];
REM Find the latest BACPAC file using the pattern matching technique
FOR /F "tokens=*" %%d IN ('DIR %DATABASEDIR%%DATABASENAME%*.bacpac /B /OD /A-D') DO SET BACPACNAME=%%d
IF "%BACPACNAME%"=="" GOTO :bacpacfilenotfound
SET DATABASEFILE=%DATABASEDIR%%BACPACNAME%
SQLCMD -S %TARGETSERVERNAME% -Q "%DropSQL%" -b -U SA -P thanVitha@2015
IF %ERRORLEVEL% NEQ 0 GOTO :error
%SQLPACKAGE% /a:Import /sf:%DATABASEFILE% /tdn:%DATABASENAME% /tsn:%TARGETSERVERNAME% /tu:sa /tp:thanVitha@2015
IF %ERRORLEVEL% NEQ 0 GOTO :ERRORBLOCK
GOTO :complete
:bacpacnotfound
ECHO bacpac file doesn't exists
EXIT /B 1
:ERRORBLOCK
ECHO import failure
EXIT /B 1
:complete
ENDLOCAL
813
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
