如何将架构和一些数据从 SQL Server 复制到另一个实例？

How to copy schema and some data from SQL Server to another instance?

提问by randbrown

My product uses a SQL Server database - each client has their own deployed instance on their own intranet. The db has about 200 tables. Most of them are configuration tables that have only a few rows, but there are a couple of transaction data tables which may have several million rows. Often I need to troubleshoot a customer's configuration problem so I need a copy of their database to work with locally on my dev system... However it may be very large due to the transaction data, which makes it very difficult for the customer to send me a backup to work with. I need a way to backup/copy/export everything EXCEPT for the large tables.

我的产品使用 SQL Server 数据库 - 每个客户端在自己的 Intranet 上都有自己部署的实例。数据库有大约 200 个表。其中大部分是只有几行的配置表，但有几个交易数据表可能有几百万行。我经常需要对客户的配置问题进行故障排除，所以我需要一份他们的数据库副本，以便在我的开发系统本地使用......但是由于交易数据，它可能非常大，这使得客户很难发送我是一个备用的工作。我需要一种备份/复制/导出除大表之外的所有内容的方法。

Ideally, the large tables really belong a separate database catalog, such that a customer could backup their "data" separately from their "configuration". However, I don't have the authority to make that type of change, so I'm looking for a good way to export or copy just the configuration parts without having to copy the whole thing.

理想情况下，大表确实属于一个单独的数据库目录，这样客户可以将他们的“数据”与他们的“配置”分开备份。但是，我无权进行此类更改，因此我正在寻找一种仅导出或复制配置部分而无需复制整个内容的好方法。

Currently I have a quick and dirty .NET app which uses a SQL Adapter and DataSet to blindly select * from all tables except the ones I want to omit, dump that to an XML file (this is my poor-man's export feature). And I have companion app I can use locally to load the XML back into a DataSet and then use SQLBulkCopy to load it into my dev database. I had to do some tricks to disable constraints and so forth, but I made it work. This gets me the data, but not the schema.... ideally I'd like a way to get the schema along with it. It is a brute force approach, and I believe there must be a simpler way. Any suggestions for a better approach?

目前我有一个快速而肮脏的 .NET 应用程序，它使用 SQL 适配器和数据集从所有表中盲目选择 *，除了我想省略的表，将其转储到 XML 文件（这是我的穷人的导出功能）。我有配套的应用程序，我可以在本地使用将 XML 加载回 DataSet，然后使用 SQLBulkCopy 将其加载到我的开发数据库中。我不得不做一些技巧来禁用约束等等，但我让它工作了。这让我得到了数据，但不是模式......理想情况下，我想要一种方法来获取模式。这是一种蛮力方法，我相信必须有一种更简单的方法。对更好的方法有什么建议吗？

The solution needs to be automated since I'm dealing with end-users who generally have very little IT support.

该解决方案需要自动化，因为我要与通常很少有 IT 支持的最终用户打交道。

(SQL 2005 and higher)

（SQL 2005 及更高版本）

采纳答案by randbrown

I solved it as follows. I created a custom winform app to export their data:

我是这样解决的。我创建了一个自定义的 winform 应用程序来导出他们的数据：

1. use SMO (Transfer class) to script the entire schema to a .sql file
2. loop through all specified tables (actually I just listed the large-growth ones to ignore, and process everything else) using: select * from
3. capture the records into DataTable objects and dump them to .xml files
4. Zip the whole folder up to be sent back to me

1. 使用 SMO（传输类）将整个架构脚本写入 .sql 文件
2. 循环遍历所有指定的表（实际上我只是列出了要忽略的大增长表，并处理其他所有表）使用：select * from
3. 将记录捕获到 DataTable 对象中并将它们转储到 .xml 文件
4. 将整个文件夹压缩后寄回给我

And I have a custom winform app I use locally on my dev system to import the data:

我有一个自定义的 winform 应用程序，我在我的开发系统本地使用它来导入数据：

1. Create an empty database
2. Run the .sql file to build the schema
3. Disable all constraints on all tables (using sp_foreachtable)
4. loop through all .xml file and bulk import them using SqlBulkCopy object
5. Re-enable all constraints (using sp_foreachtable)

1. 创建一个空数据库
2. 运行 .sql 文件以构建架构
3. 禁用所有表的所有约束（使用 sp_foreachtable）
4. 循环遍历所有 .xml 文件并使用 SqlBulkCopy 对象批量导入它们
5. 重新启用所有约束（使用 sp_foreachtable）

This approach works well for my database, but I would not necessarily advise this for some database designs. In my case, there are many "small" configuration tables, and only a few "large" tables with minimal relations between them so I can easily ignore the large ones. If there were more complex relations it would probably not be a good solution...

这种方法适用于我的数据库，但对于某些数据库设计，我不一定建议这样做。就我而言，有许多“小”配置表，只有几个“大”表之间的关系最小，因此我可以轻松忽略大表。如果有更复杂的关系，它可能不是一个好的解决方案......

Seems to be working great so far. I was hoping to find a good way to do this without writing custom code, but I got it working with only a few hours of dev time.

到目前为止似乎工作得很好。我希望找到一种不用编写自定义代码的好方法，但我只用了几个小时的开发时间就让它工作了。

FYI, I almost got it working with sqlpubwiz, but the option to script data is all-or-nothing... you can't ignore specific tables... that was a deal-breaker for my app. The Transfer object in SMO is a very useful class - it only requires a few lines of code to script an entire database schema including all dependencies in the correct creation order to recreate the database from scratch!

仅供参考，我几乎让它与 sqlpubwiz 一起工作，但是脚本数据的选项是全有或全无的……你不能忽略特定的表……这对我的应用程序来说是一个交易破坏者。SMO 中的 Transfer 对象是一个非常有用的类——它只需要几行代码就可以编写整个数据库模式的脚本，包括按正确创建顺序的所有依赖项，以从头开始重新创建数据库！

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.transfer.aspx

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.transfer.aspx

回答by JohnD

Could you generate the scripts from SSMS directly?

你能直接从 SSMS 生成脚本吗？

1. Right click the database
2. Select Tasks -> Generate Scripts
3. (Click next if you get the intro screen)
4. Select "Select specific database objects"
5. Pick the objects to generate scripts for (tables, stored procedures, etc...)
6. Click Next, then specify the output filename
7. Click Finish to generate the script

1. 右键单击数据库
2. 选择任务 -> 生成脚本
3. （如果您看到介绍屏幕，请单击下一步）
4. 选择“选择特定的数据库对象”
5. 选择要为其生成脚本的对象（表、存储过程等...）
6. 单击下一步，然后指定输出文件名
7. 点击完成生成脚本

This will generate the schemas only. If you want to do data generating scripts as well, in step 6) click the Advanced button and scroll down to the "Types of data to script" and change it from "Schema only" to "Data only" or "Schema and data"

这将仅生成模式。如果您还想执行数据生成脚本，请在第 6 步中单击“高级”按钮并向下滚动到“要编写脚本的数据类型”并将其从“仅限架构”更改为“仅限数据”或“架构和数据”

In your situation, you could do "Schema and data" for all of your small config tables, then do "Schema only" for the large tables where you don't want to export the data.

在您的情况下，您可以对所有小型配置表执行“架构和数据”，然后对不想导出数据的大型表执行“仅架构”。

I know this isn't totally automated however it's pretty close. If you want to look at automating this even further, check out this thread. Not sure if this works on SQL 2005:

我知道这不是完全自动化的，但它非常接近。如果您想进一步了解自动化，请查看此线程。不确定这是否适用于 SQL 2005：

How can I automate the "generate scripts" task in SQL Server Management Studio 2008?

如何在 SQL Server Management Studio 2008 中自动执行“生成脚本”任务？

回答by Stan Petersen

Just a couple comments since we had similar problem in the past.

因为我们过去遇到过类似的问题，所以只有一些评论。

Generate scripts option in SSMS is great for small databases where you can manually re-order the individual scripts and avoid dependency issues. Not sure why so many people suggest using this feature that has dependency issues.

SSMS 中的生成脚本选项非常适合小型数据库，您可以在其中手动重新排序各个脚本并避免依赖性问题。不知道为什么这么多人建议使用这个有依赖性问题的功能。

For larger databases only viable solution is something like you created yourself or using third party tools.

对于较大的数据库，唯一可行的解​​决方案是您自己创建或使用第三方工具。

We ended up using this third party tool. SQL Packager worked a bit better at the time we evaluated but we managed to get the other tool basically for free…

我们最终使用了这个第三方工具。在我们评估时，SQL Packager 工作得更好一些，但我们设法基本上免费获得了另一个工具......

Anyway, hope this helps even though it's a late answer/comment…

无论如何，希望这会有所帮助，即使这是一个迟到的答案/评论......

回答by David Atkinson

Here at Red Gate we have a tool called SQL Packager that has a command line that can be automated to perform this task. This uses the SQL Compare and SQL Data Compare engines under the hood. I'd be interested to get your feedback on whether this satisfies your use case.

在 Red Gate，我们有一个名为 SQL Packager 的工具，它有一个可以自动执行此任务的命令行。这在后台使用 SQL 比较和 SQL 数据比较引擎。我很想得到您关于这是否满足您的用例的反馈。

http://www.red-gate.com/products/sql-development/sql-packager/

http://www.red-gate.com/products/sql-development/sql-packager/