windows 相当于 SQL Server 的 mysqldump
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/85186/
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
mysqldump equivalent for SQL Server
提问by David
Is there an equivalent schema & data export/dumping tool for SQL Server as there is for MySQL with mysqldump. Trying to relocate a legacy ASP site and I am way out of happy place with working on a windows server.
是否有用于 SQL Server 的等效架构和数据导出/转储工具，就像用于带有 mysqldump 的 MySQL 一样。试图重新定位旧的 ASP 站点，但我对在 Windows 服务器上工作感到很不满意。
Note: The DTS export utility own seems to export data, without table defs. Using the Enterprise Manager and exporting the db gets closer with exporting the schema & data... but still misses stored procedures.
注意：DTS 导出实用程序自己似乎导出数据，没有表定义。使用企业管理器和导出数据库与导出模式和数据更接近......但仍然错过了存储过程。
Basically looking for a one does it all solution that grabs everything I need at once.
基本上是在寻找一个能一次性抓住我需要的一切的解决方案。
回答by dallin
To do this really easily with SQL Server 2008 Management Studio:
要使用 SQL Server 2008 Management Studio 轻松完成此操作：
1.) Right click on the database (not the table) and select Tasks -> Generate Scripts
1.) 右键单击数据库（不是表）并选择任务 -> 生成脚本
2.) Click Next on the first page
2.) 在第一页点击下一步
3.) If you want to copy the whole database, just click next. If you want to copy specific tables, click on "Select Specific Database Objects", select the tables you want, and then click next.
3.) 如果要复制整个数据库，只需单击下一步。如果要复制特定表，请单击“选择特定数据库对象”，选择所需的表，然后单击下一步。
4.) "Save to File" should be selected. IMPORTANT: Click the Advanced button next to "Save to File", find "Types of data to script", and change "Schema only" to "Schema and data" (if you want to create the table) or "Data only" (if you're copying data to an existing table).
4.) 应选择“保存到文件”。重要提示：单击“保存到文件”旁边的“高级”按钮，找到“要脚本的数据类型”，然后将“仅架构”更改为“架构和数据”（如果要创建表）或“仅数据”（如果您将数据复制到现有表中）。
5.) Click through the rest and you're done! It will save as a .sql file.
5.) 点击其余部分，你就完成了！它将另存为 .sql 文件。
回答by Erick
The easiest way is the sql server database publishing wizard.
最简单的方法是sql server 数据库发布向导。
- Open source
- Free
- Does exactly what you want
- Developed by microsoft
- 开源
- 自由
- 正是你想要的
- 由微软开发
It does not have all the features of mysqldump but it is close enough.
它没有 mysqldump 的所有功能，但已经足够接近了。
http://www.codeplex.com/sqlhost/wiki/view.aspx?title=database%20publishing%20wizard
http://www.codeplex.com/sqlhost/wiki/view.aspx?title=database%20publishing%20wizard
回答by Alex Argo
The easiest way to move a Database would be to use SQL Server Management Studio to Export the database to another server, or if that doesn't work, make a backup like other's had suggested and restore it elsewhere.
移动数据库的最简单方法是使用 SQL Server Management Studio 将数据库导出到另一台服务器，或者如果这不起作用，请按照其他人的建议进行备份并在其他地方恢复。
If you are looking for a way to dump the table structure to SQL as well as create insert scripts for the data a good free option would be to use amScript and amInsert from http://www.asql.biz/en/Download2005.aspx.
如果您正在寻找一种将表结构转储到 SQL 以及为数据创建插入脚本的方法，一个不错的免费选择是使用来自http://www.asql.biz/en/Download2005.aspx 的amScript 和 amInsert .
If you want a good pay version I would check out Red-Gate SQL Compare and Red-Gate SQL-Data Compare. These tools are probably overkill though and probably a bit pricey if you don't intend to use them a lot. I would think it would mostly be relegated to DBAs. You can look at the Red-Gate tools at http://www.red-gate.com/.
如果你想要一个好的付费版本，我会查看 Red-Gate SQL Compare 和 Red-Gate SQL-Data Compare。不过，这些工具可能有点矫枉过正，如果您不打算经常使用它们，可能会有点贵。我认为它主要会被降级为 DBA。您可以在http://www.red-gate.com/ 上查看 Red-Gate 工具。
回答by Daniel
回答by Daniel
Even easier is to use the SMO API. It lets you do exactly like mysqldump, and even better. Here is a code example:
使用 SMO API 更容易。它可以让你像 mysqldump 一样做，甚至更好。这是一个代码示例：
http://samyem.blogspot.com/2010/01/automate-sql-dumps-for-sqlserver.html
http://samyem.blogspot.com/2010/01/automate-sql-dumps-for-sqlserver.html
回答by Matt Rogish
Well, Mysqldump is a series of SQL statements. You can do this with DTS, but why not just create a backup and restore it on your new machine?
好吧，Mysqldump 是一系列的 SQL 语句。你可以用 DTS 做到这一点，但为什么不创建一个备份并在你的新机器上恢复它呢？
If you want to do it via SQL: http://msdn.microsoft.com/en-us/library/aa225964(SQL.80).aspx
如果您想通过 SQL 执行此操作：http: //msdn.microsoft.com/en-us/library/aa225964(SQL.80).aspx
Or just right click the DB and hit Tasks -> Backup (http://msdn.microsoft.com/en-us/library/ms187510.aspx)
或者只需右键单击数据库并点击任务 -> 备份（http://msdn.microsoft.com/en-us/library/ms187510.aspx）
回答by SQLMenace
easiest would be a backup and restore or detach and attach
最简单的方法是备份和恢复或分离和附加
or script out all the tables and BCP out the data then BCP in the data on the new server
或编写所有表的脚本，然后 BCP 输出数据，然后 BCP 在新服务器上的数据中
or use DTS/SSIS to do this
或使用 DTS/SSIS 来做到这一点
回答by StingyHyman
SQL Enterprise manager or SQL Server Management studio have wizard based approaches, and the latter will generate the scripts so you can see how its done.
SQL 企业管理器或 SQL Server 管理工作室有基于向导的方法，后者将生成脚本，以便您可以看到它是如何完成的。
You could also use the BACKUP and RESTORE commands. More detail here: http://msdn.microsoft.com/en-us/library/ms189826.aspx
您还可以使用 BACKUP 和 RESTORE 命令。更多详细信息：http: //msdn.microsoft.com/en-us/library/ms189826.aspx
回答by Mike Dimmick
If you can get DTS or Integration Services to connect to both servers, you can use the wizards to 'copy objects' from one server to another. 'Copy Database' requires that the two servers can authenticate with each other, which typically means being on the same domain and that the service runs under a domain logon.
如果您可以让 DTS 或 Integration Services 连接到两台服务器，则可以使用向导将对象从一台服务器“复制”到另一台服务器。“复制数据库”要求两台服务器可以相互验证，这通常意味着位于同一个域中并且服务在域登录下运行。
Otherwise, you can generate a script for the schema, and you can use an Integration Services/DTS package to export data to a file, then import it on the other.
否则，您可以为架构生成脚本，并且可以使用 Integration Services/DTS 包将数据导出到一个文件，然后将其导入另一个文件。
We now generally use SQL Compareand SQL Data Compare. Red Gate's SQL Packagermight also be an option.
我们现在一般使用SQL 比较和SQL 数据比较。Red Gate 的SQL Packager也可能是一个选项。
回答by WGroleau
Two things a backup/restore won't do:
备份/恢复不会做的两件事：
- Get off of a Microsoft server, which was part of the original question
- Help quickly find a structural difference between two DBs that are supposed to have the same structure when one of them is running slowly. Unix diff, or sdiff, ignoring white space but need a way to make input files.
- 离开微软服务器，这是原始问题的一部分
- 帮助快速找到两个 DB 之间的结构差异，这些 DB 在其中一个运行缓慢时应该具有相同的结构。Unix diff 或 sdiff，忽略空白但需要一种方法来制作输入文件。
