如何获取 SQL Server 数据的脚本？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2321052/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get script of SQL Server data?
提问by Jared
I'm looking for a way to do something analogous to the MySQL dump from SQL Server. I need to be able to pick the tables and export the schema and the data (or I can export the schema via SQL Server Management Studio and export the data separately somehow).
我正在寻找一种方法来执行类似于 SQL Server 中的 MySQL 转储的操作。我需要能够选择表并导出架构和数据（或者我可以通过 SQL Server Management Studio 导出架构并以某种方式单独导出数据）。
I need this data to be able to turn around and go back into SQL Server so it needs to maintain GUIDs/uniqueidentifiers and other column types.
我需要这些数据能够返回并返回到 SQL Server，因此它需要维护 GUID/唯一标识符和其他列类型。
Does anyone know of a good tool for this?
有谁知道一个很好的工具吗？
回答by Daniel Vassallo
From the SQL Server Management Studio you can right click on your database and select:
在 SQL Server Management Studio 中，您可以右键单击您的数据库并选择：
Tasks -> Generate Scripts
Then simply proceed through the wizard. Make sure to set 'Script Data' to TRUE when prompted to choose the script options.
然后只需继续通过向导。当提示选择脚本选项时，请确保将“脚本数据”设置为 TRUE。
SQL Server 2008 R2
SQL Server 2008 R2
Further reading:
进一步阅读：
回答by bluntfakie
SQL Server Management Studio
SQL Server 管理工作室
This is your best tool for performing this task. You can generate a script that will build whichever tables you wish from a database as well as insert the data in those tables (as far as I know you have to export all of the data in the selected tables however).
这是执行此任务的最佳工具。您可以生成一个脚本，该脚本将从数据库中构建您想要的任何表，并将数据插入这些表中（据我所知，您必须导出所选表中的所有数据）。
To do this follow these steps:
为此，请执行以下步骤：
Right-click on your database and select Tasks > Generate Scripts
In the Generate and Publish Scripts wizard, select the "Select specific database objects" option
Expand the "Tables" tree and select all of the tables you wish to export the scheme and data for, then click Next
In the next screen choose how you wish to save the script (the Output Type must remain set as "Save scripts to a specific location"), then click the Advanced button in the top right corner
In the newly opened window, under the General section is a setting called "Types of data to script", set this to "Scheme and data" and click OK
Click Next, review the export summary and click Next again. This will generate the script to your selected destination.
右键单击您的数据库并选择任务 > 生成脚本
在生成和发布脚本向导中，选择“选择特定数据库对象”选项
展开“表格”树并选择您希望为其导出方案和数据的所有表格，然后单击下一步
在下一个屏幕中选择您希望如何保存脚本（输出类型必须保持设置为“将脚本保存到特定位置”），然后单击右上角的高级按钮
在新打开的窗口中，在 General 部分下是一个名为“Types of data to script”的设置，将其设置为“Scheme and data”，然后单击“确定”
单击下一步，查看导出摘要并再次单击下一步。这将为您选择的目的地生成脚本。
To restore your database, simply create a new database and change the first line of your generated script to USE [Your.New.Database.Name], then execute. Your new database will now have all of the tables and data you selected from the original database.
要恢复数据库，只需创建一个新数据库并将生成的脚本的第一行更改为USE [Your.New.Database.Name]，然后执行。您的新数据库现在将拥有您从原始数据库中选择的所有表和数据。
回答by SilverSideDown
I had a hell of a time finding this option in SQL Management Studio 2012, but I finally found it. The option is hiding in the Advanced button in the screen below.
我花了很长时间在 SQL Management Studio 2012 中找到这个选项，但我终于找到了。该选项隐藏在下面屏幕的“高级”按钮中。
I always assumed this contained just assumed advanced options for File generation, since that's what it's next to, but it turns out someone at MS is just really bad at UI design in this case. HTH somebody who comes to this thread like I did.
我一直认为这只是假设的文件生成高级选项，因为这就是它旁边的内容，但事实证明，在这种情况下，MS 的某个人在 UI 设计方面非常糟糕。HTH 像我一样来到这个线程的人。
回答by Maisie John
If you want to script all table rows then Go with Generate Scripts as described by Daniel Vassallo. You can't go wrong here
如果您想编写所有表格行的脚本，那么请按照 Daniel Vassallo 的描述使用 Generate Scripts。你不会在这里出错
Else Use third party tools such as ApexSQL Scriptor SSMS Toolpack for more advanced scripting that includes some preprocessing, selective scripting and more.
其他 使用第三方工具（例如ApexSQL Script或 SSMS Toolpack）进行更高级的脚本编写，包括一些预处理、选择性脚本编写等。
回答by CTKeane
Check out SSMS Tool Pack. It works in Management Studio 2005 and 2008. There is an option to generate insert statements which I've found helpful moving small amounts of data from one system to another.
查看SSMS 工具包。它适用于 Management Studio 2005 和 2008。有一个选项可以生成插入语句，我发现这有助于将少量数据从一个系统移动到另一个系统。
With this option you will have to script out the DDL separately.
使用此选项，您必须单独编写 DDL 脚本。
回答by Greg
SqlPubWiz.exe (for me, it's in C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Publishing\1.2>)
SqlPubWiz.exe（对我来说，它在 C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Publishing\1.2>）
Run it with no arguments for a wizard. Give it arguments to run on commandline.
运行它，不带任何参数作为向导。为其提供参数以在命令行上运行。
SqlPubWiz.exe script -C "<ConnectionString>" <OutputFile>
回答by Vaelen
I know this has been answered already, but I am here to offer a word of warning. We recently received a database from a client that has a cyclical foreign key reference. The SQL Server script generator refuses to generate the data for databases with cyclical references.
我知道这已经得到了回答，但我在这里提出警告。我们最近从一个客户那里收到了一个数据库，它有一个循环外键引用。SQL Server 脚本生成器拒绝为具有循环引用的数据库生成数据。
回答by KM.
BCP can dump your data to a file and in SQL Server Management Studio, right click on the table, and select "script table as" then "create to", then "file..." and it will produce a complete table script.
BCP 可以将您的数据转储到一个文件中，在 SQL Server Management Studio 中，右键单击该表，然后选择“脚本表为”，然后选择“创建到”，然后选择“文件...”，它将生成一个完整的表脚本。
BCP info
https://web.archive.org/web/1/http://blogs.techrepublic%2ecom%2ecom/datacenter/?p=319
http://msdn.microsoft.com/en-us/library/aa174646%28SQL.80%29.aspx
BCP 信息
https://web.archive.org/web/1/http://blogs.techrepublic%2ecom%2ecom/datacenter/?p=319
http://msdn.microsoft.com/en-us/library/ aa174646%28SQL.80%29.aspx
