SQL Server 2012：使用命令行生成脚本

Question

我正在使用SQL Server 2012。

SQL Server Management Studio有一个选项，可以右键单击数据库，然后选择任务和生成脚本。

有没有办法通过命令行自动化这个过程？

我想创建一个包含整个数据库架构和数据的脚本。

像ScriptDB和sqlpubwiz.exe这样的工具似乎都针对SQL Server 2005。那么SQL Server 2012呢？

- Harald

Answer 1

最好使用PowerShell - 如果您经常使用它的话。您可以参考使用Powershell和SMO进行自动脚本生成。

此外，当使用PowerShell时，SQL Server PowerShell扩展(SQLPSX)非常有价值。所有模块都有帮助文件，例如Get-SqlScripter。

Get-SqlDatabase -dbname database1 -sqlserver server | Get-SqlTable | Get-SqlScripter | Set-Content -Path D:\scripts\script.sql
Get-SqlDatabase -dbname database1 -sqlserver server | Get-SqlStoredProcedure | Get-SqlScripter
Get-SqlDatabase -dbname database1 -sqlserver server | Get-SqlView | Get-SqlScripter

对于第三方工具，强烈推荐以下几款（市面上有很多第三方工具可供选择，但是以下我个人用过的都非常棒）:

• Redgate的SQL Compare和Data Compare
• Mladen Prajdić的SSMS Tools Pack

- Kin Shah

---

SQLPSX自2018年2月以来没有进行任何提交... - Macke

你正在评论一个2014年写的答案。事情已经发生了巨大变化。去看看dbatools，它涵盖了你所需的大部分用例。 - Kin Shah

确实如此。虽然这在我的谷歌搜索结果中排名很高，但我想帮助其他通过相同方式到达的人。 - Macke

他的评论是为了帮助保持答案的相关性和更新。 - Mauricio Gracia Gutierrez

Answer 2

最近，来自微软的Tara Raj宣布，微软SQL团队发布了一套命令行工具，可以生成符合你要求的T-SQL脚本。

mssql-scripter是SSMS中广泛使用的生成脚本向导体验的多平台命令行等效工具。

您可以在Linux、macOS和Windows上使用mssql-scripter为运行在任何地方的SQL Server、Azure SQL Database和Azure SQL Data Warehouse中的数据库对象生成数据定义语言(DDL)和数据操作语言(DML) T-SQL脚本。您可以将生成的T-SQL脚本保存到.sql文件中，或者将其传输给标准*nix实用程序(例如sed、awk、grep)进行进一步转换。您可以编辑生成的脚本，或将其检入源代码控制，并随后使用标准的多平台SQL命令行工具(如sqlcmd)在现有的SQL数据库部署流程和DevOps管道中执行该脚本。

mssql-scripter使用Python构建，并融入了新的Azure CLI 2.0工具的可用性原则。源代码可以在Github上找到https://github.com/Microsoft/sql-xplat-cli，我们欢迎您的贡献和拉取请求！

以下是一些使用示例：

为Adventureworks数据库中的所有数据库对象(默认)生成DDL脚本，并输出到stdout。

$ mssql-scripter -S localhost -d AdventureWorks -U sa

为Adventureworks数据库中的所有数据库对象生成DDL脚本，并为所有表生成DML脚本（INSERT语句），并将脚本保存到文件中。

$ mssql-scripter -S localhost -d AdventureWorks -U sa –schema-and-data  > ./output.sql

- BradC

---

听起来很不错，有一种正式的感觉，但是自2018年以来没有任何提交让我感到不安。 - Macke

Answer 3

我写了一个开源的命令行实用程序，名为SchemaZen，可以做到这一点。它比从管理工作室脚本化要快得多，并且输出更适合版本控制。它支持脚本化模式和数据。

要生成脚本，请运行以下命令：

schemazen.exe script --server localhost --database db --scriptDir c:\somedir 然后，要根据脚本重新创建数据库，请运行以下命令：

schemazen.exe create --server localhost --database db --scriptDir c:\somedir

- Seth Reno

Answer 4

只是一个更新：在当前版本的SQL Server powershell模块中（从SQL Server 2014开始，我相信。在SSMS 17上进行了测试），大多数这些选项都是原生命令和方法。

例如，您可以使用Get-SqlDatabase以及.Script()和.EnumScript()等方法。这非常有用且简单，特别是如果您想要更精细的方法（特定表和其他对象）。

例如，这将为用户定义的函数生成CREATE脚本并将其保存到文件中：

$Database = Get-SqlDatabase -ServerInstance $YourSqlServer -Name $YourDatabaseName

$MyFuncs = $Database.UserDefinedFunctions | Where Schema -eq "dbo"
$MyFuncs.Script() | Out-File -FilePath ".\SqlScripts\MyFunctions.sql"

如果你想要脚本化数据和元素，比如索引、键、触发器等等，你需要指定脚本选项，就像这样：

$scriptOptions = New-Object -TypeName Microsoft.SqlServer.Management.Smo.ScriptingOptions

$scriptOptions.NoCollation = $True
$scriptOptions.Indexes = $True
$scriptOptions.Triggers = $True
$scriptOptions.DriAll = $True
$scriptOptions.ScriptData = $True

$Database.Tables.EnumScript($scriptOptions) | Out-File -FilePath ".\AllMyTables.sql"

请注意，Script()方法不支持脚本数据。对于表格，请使用EnumScript()方法。

一个单独的表格：

($Database.Tables | Where Name -eq "MyTableName").EnumScript($scriptOptions)

所有的视图，每个视图保存一个文件，DROP和CREATE脚本：

ForEach ($view in $($Database.Views | Where Schema -eq "dbo")) {

    "`nIF OBJECT_ID('$($view.Name)') IS NOT NULL DROP VIEW $($view.Name);`n`n" | Out-File -FilePath ".\SqlScripts\$($view.Name).sql"    
    $view.Script() | Out-File -FilePath ".\SqlScripts\$($view.Name).sql" -Append
}

希望这能帮到你。

- Victor Lopes

---

真棒。2020年的正确解决方案！然而，顺序与SSMS中不同，使用EnumScripts导出时遇到了一些依赖问题。对此你有什么想法吗？ - Macke

不用了。使用WithDependencies似乎是个好选择。 - Macke

WithDependencies只适用于一个表，而不是所有表，因为它会为每个导出重新导出依赖的表。因此，应该使用Scripter对象的依赖解析函数，就像这里所做的一样：http://patlau.blogspot.com/2012/09/generate-sqlserver-scripts-with.html - Macke

Answer 5

您可以使用xSQL Schema Compare和xSQL Data Compare的命令行版本。通过命令行版本，您可以定期安排对空数据库进行数据和架构比较，并始终拥有构建最新版本数据库精确副本所需的脚本。

披露声明：我与xSQL有关联。

- Endi Zhupani