在 Management Studio 中创建更改脚本？
我想知道是否有一种方法可以自动将我对列、表、关系等所做的所有更改附加到脚本文件中...
问题是我正在测试数据库上进行很多不同的更改，这个想法当我将测试数据库移至生产环境时，将应用此更改脚本...因此保留生产数据，但应用所有架构和对象更改。
有没有简单的方法可以做到这一点？它还可以迁移数据库图表更改吗？
我已经看到如何在每次进行更改时创建更改脚本，但这意味着我必须复制并粘贴到主文件中。其实很简单！
我只是想知道我是否错过了什么？
I was wondering if there is a way to automatically append to a script file all the changes I am making to my columns, tables, relationships etc...
The thing is I am doing a lot of different changes on a TEST db and the idea will be to apply this change script when I move the test db to production... hence keeping production data but applying all schema and object changes.
Is there an easy way to do this? Can it also migrate database diagram changes?
I have seen how you can create a change script each time I do a change but this means I have to copy and paste into a master file. Actually pretty easy!
I was just wondering if I was missing something?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
不要使用 UI 对测试服务器进行更改。编写脚本并将其置于源代码控制之下。您可以从实时数据的备份开始测试您的脚本，并且可以调整您的脚本，直到它们达到所需的结果。然后您可以签入脚本以供参考，然后将它们应用到实时服务器上。请参阅这篇文章版本控制和您的数据库。
顺便说一句，请查看 SSMS 工具包，我认为它可能会满足您的需求（我不确定）。尽管如此，我的建议仍然是：对架构进行版本控制、使用显式创建/保存的脚本、使用源代码控制。
Do not make changes to the test server using the UI. Write scripts and keep them under source control. You can test your scripts starting from backups of the live data and you can tune yoru scripts untill they achieve the desired result. Then you can check in the scripts for reference and later apply them on the live server. See this article Version Control and Your Database.
BTW, check out the SSMS toolpack, I think it may do what you want (I'm not sure). My advice stand none the less: version your schema, use explicitly created/saved scripts, use source control.
无法在 SSMS 中直接生成“delta”脚本。
但是，如果每次发布更改时，都使用 SQL Server 数据库发布向导 您应该能够提取版本之间的差异并以这种方式获取增量。
如果钱不是问题，您可以购买 Visual Studio Team System Database Architect 版本，并使用其出色的数据库比较工具来生成和版本控制您想要的差异。
There's no way to directly generate a "delta" script in SSMS.
However, if every time you publish changes, you script out the entire database, including data, to SQL using the SQL Server Database Publishing Wizard you should be able to extract diffs between the versions and get your deltas that way.
If money is no object, you can purchase Visual Studio Team System Database Architect edition and use its fantastic database comparison tools to generate and version control exactly the diffs you want.
尝试使用 SQL Server 2005 附带的 TableDiff 。
SQL Server 2005 TableDiff 实用程序
tablediff 实用程序
Try using TableDiff , that came with SQL Server 2005.
SQL Server 2005 TableDiff Utility
tablediff Utility
我们有一个流程，当开发人员完成更改后，他们将其编写脚本并将其签入 Subversion。在 Subversion 中，我们有一个用于存放表、存储过程、数据等的文件夹。他们将其脚本化，以便可以重复（即，如果新数据已经存在，则不要插入新数据。）无论如何，这样做很重要，因此您可以保留数据库中给定对象的更改历史记录。
过去，我们只需将想要编写的每个文件输入到文本文件中（即 FileListV102.txt）。当我们准备发布版本时，我们会对所有文件（当时来自 VSS）进行“获取最新”操作。然后，我们有一个简单的实用程序，可以读取“文件列表”文件并依次打开每个文件将它们连接到输出文件中。这很容易编码。
我们已经超越了这一点，现在我们有了一个发布管理工具（可以在此处找到，并将于 9 月中旬发售），这会获取所有文件并从中创建一个大的 SQL 脚本文件。它按照您期望的基于文件夹名称的顺序执行此操作 - 因此“Tables”文件夹中找到的文件先于“Data”文件夹中的文件执行，依此类推。
无论哪种方式，一旦完成，您就会有一个大的 SQL脚本文件，然后您可以将其应用于新的生产副本，这就是您测试的对象。
We have the process where when a developer gets done with a change, they then script it out and check it into Subversion. In Subversion we have a folder for Tables, Stored Procs, Data, etc. They script it out so it is repeatable (i.e. don’t insert the new data if it is already there.) This is important to do anyway so you keep the history of changes for a given object in the database.
In the past, we would just enter each of the files that we wanted scripted out into a text file (i.e. FileListV102.txt). When we were ready to make a release we would do “get latest” on all of the files (from VSS back then.) We then had a simple utility that would read the “file list” file and open each of those files in turn concatenating them into an output file. That is pretty easy to code.
We outgrew that and now we have a release management tools (which can be found here and will be on sale mid September), that takes all of the files and creates a big SQL script file out of it. It does it in the order that you would expect based on the folder names – so files found in the "Tables" folder are done before those in the "Data" folder, etc.
Either way, once you are done you have a big SQL script file that you can then apply to a fresh copy of production and that is what you test against.
我知道我来晚了，但我只是想补充一点，有数十种第三方产品。有些非常好，有些非常便宜或免费，还有一些是混合的。我在这里列出了 22 个：
http://bertrandaaron.wordpress.com/2012/04/20/re-blog-the-cost-of-reinventing-the-wheel/
I know I'm way late to the party, but I just wanted to add that there are tens of third party products out there. Some are very good, some are very cheap or free, and some are a mixture. I listed 22 here:
http://bertrandaaron.wordpress.com/2012/04/20/re-blog-the-cost-of-reinventing-the-wheel/
我们一直在使用一个名为 Kal Admin 的相对较新的软件。
它具有更改管理功能，可以轻松地将选定的更改分发到其他数据库。我们过去通过比较两个数据库来做到这一点，但它不能满足我们对变更跟踪的需求。
BTW Kal Admin 还具有元数据和数据比较功能。
We have been using a relatively new software called Kal Admin.
It has Change Management feature and let distributing selected changes to other databases very easily. We used to do it by comparing two databases but it not satisfy our need for change tracking.
BTW Kal Admin has Metadata and data compare capabilities as well.