This is a tough problem, plain and simple. The tools mentioned in other answers can definitely help, but you're still left to do a lot yourself.
I actually have a table in my schema that stores an ID for each change script (which matches the case number in my issue/bug tracking system). At the end of each change script I insert the ID into that table. I have a separate script that checks to see what's been done on any specific instance of the database. It would be feasible to automate running each of the scripts that haven't been run yet, but I like to run change scripts manually in case anything goes wrong.
Rollbacks can be almost impossible, especially since many schema changes require some sort of data migration. I have found that best practice with any changes to the schema are to make them backwards-compatible. Never rename a column or table (at least at first). Only add things, and make all new adds nullable. A rollback script simply removes the new stuff, if you realize something isn't quite right. Of course you end up with old, unused columns and tables, so you write a second script that is run after your current release is considered stable, which gets rid of the old stuff.
I you want to write all the scripts yourself you can take advantage of the SQL Server 2005 TableDiff Utility that will give you a lot of flexibility. Some usage examples can be find here
OK so I didn't like any of the options that I found out there. I found some good tools to create the scripts but nothing that would track the version in the database and run the new scripts. Anyway I went ahead and rolled my own Open Source tool to get the job done. If you want to use it you can download if from CodePlex its called KissDB. I also threw up a blog post about it at my blog blog.RunXc
We have our developers check the database change scripts into Subversion. All scripts are repeatable so you can run them multiple times without error. We also link the change scripts to issue items or bug ids so we can hold back a change set if needed. We then have an automated build process that kicks out a single SQL script file with all of the changes sorted appropriately. This single file is then used to promote the changes to the Test, QA and Production environments. We think this is the best approach with enterprise developers. More details on how we do it HERE Your feedback would be appreciated.
发布评论
评论（9）
您看过 Open DBDiff 了吗？这可能是一个很好的起点。
如果您改变了对开源的想法，Red Gate 的 SQL Compare 是最好的选择海事组织的方法。
Have you looked at Open DBDiff yet? It may be a good place to start.
if you change your mind about open source, Red Gate's SQL Compare is the way to go IMO.
尝试 dbdeploy.NET
http://sourceforge.net/projects/dbdeploy-net/
Try dbdeploy.NET
http://sourceforge.net/projects/dbdeploy-net/
SourceForge 上有一个非常有趣的项目，名为 SQLRunner - 它是 C#，它是 .NET，它已经过去了“pre -alpha”:-)
我自己没有使用过它，但仍然 - 看起来相当不错，恕我直言。
马克
There's a pretty interesting project called SQLRunner on SourceForge - it's C#, it's .NET, it's past "pre-alpha" :-)
I've not used it myself, but still - looks quite good, IMHO.
Marc
这是一个棘手的问题，简单明了。其他答案中提到的工具肯定会有所帮助，但您仍然需要自己做很多事情。
实际上，我的架构中有一个表，用于存储每个更改脚本的 ID（与我的问题/错误跟踪系统中的案例编号相匹配）。在每个更改脚本的末尾，我将 ID 插入到该表中。我有一个单独的脚本，用于检查数据库的任何特定实例上执行的操作。自动运行每个尚未运行的脚本是可行的，但我喜欢手动运行更改脚本，以防出现问题。
回滚几乎是不可能的，特别是因为许多模式更改需要某种数据迁移。我发现对架构进行任何更改的最佳实践是使它们向后兼容。切勿重命名列或表（至少一开始）。只添加内容，并使所有新添加的内容可为空。如果您意识到某些事情不太正确，回滚脚本只会删除新内容。当然，您最终会得到旧的、未使用的列和表，因此您编写了第二个脚本，该脚本在当前版本被认为是稳定的之后运行，从而摆脱了旧的东西。
This is a tough problem, plain and simple. The tools mentioned in other answers can definitely help, but you're still left to do a lot yourself.
I actually have a table in my schema that stores an ID for each change script (which matches the case number in my issue/bug tracking system). At the end of each change script I insert the ID into that table. I have a separate script that checks to see what's been done on any specific instance of the database. It would be feasible to automate running each of the scripts that haven't been run yet, but I like to run change scripts manually in case anything goes wrong.
Rollbacks can be almost impossible, especially since many schema changes require some sort of data migration. I have found that best practice with any changes to the schema are to make them backwards-compatible. Never rename a column or table (at least at first). Only add things, and make all new adds nullable. A rollback script simply removes the new stuff, if you realize something isn't quite right. Of course you end up with old, unused columns and tables, so you write a second script that is run after your current release is considered stable, which gets rid of the old stuff.
如果您想自己编写所有脚本，您可以利用 SQL Server 2005 TableDiff 实用程序 将为您提供很大的灵活性。一些用法示例可以找到 这里
I you want to write all the scripts yourself you can take advantage of the SQL Server 2005 TableDiff Utility that will give you a lot of flexibility. Some usage examples can be find here
如果您将给定版本的所有脚本都放在一个文件夹中，则可以将其作为批处理文件运行（如果您将其放入该文件夹中）：
抱歉，我不记得从哪里得到这个文件，否则我会给予信任。
If you have all of your scripts for a given version in a folder, you can run this as a batch file if you place it in that folder:
Sorry, I don't remember where I got this from or I would give credit.
好吧，我不喜欢我在那里找到的任何选项。我找到了一些很好的工具来创建脚本，但没有任何工具可以跟踪数据库中的版本并运行新脚本。不管怎样，我继续推出自己的开源工具来完成工作。如果您想使用它，您可以从 CodePlex 下载它，名为 KissDB。我还在我的博客上发表了一篇关于它的博客文章 blog.RunXc
OK so I didn't like any of the options that I found out there. I found some good tools to create the scripts but nothing that would track the version in the database and run the new scripts. Anyway I went ahead and rolled my own Open Source tool to get the job done. If you want to use it you can download if from CodePlex its called KissDB. I also threw up a blog post about it at my blog blog.RunXc
我们让开发人员将数据库更改脚本检查到 Subversion 中。所有脚本都是可重复的，因此您可以多次运行它们而不会出错。我们还将更改脚本链接到问题项或错误 ID，以便我们可以在需要时保留更改集。然后，我们有一个自动构建过程，该过程会生成一个 SQL 脚本文件，其中所有更改都进行了适当排序。然后，使用该单个文件来促进对测试、QA 和生产环境的更改。我们认为这是企业开发人员的最佳方法。有关我们如何做到这一点的更多详细信息此处我们将不胜感激。
We have our developers check the database change scripts into Subversion. All scripts are repeatable so you can run them multiple times without error. We also link the change scripts to issue items or bug ids so we can hold back a change set if needed. We then have an automated build process that kicks out a single SQL script file with all of the changes sorted appropriately. This single file is then used to promote the changes to the Test, QA and Production environments. We think this is the best approach with enterprise developers. More details on how we do it HERE Your feedback would be appreciated.
使用液体碱。虽然它是Java。
是的，您可以使用您选择的 SQL 变体编写迁移脚本。
它经过充分测试并被很多人使用。
Use liquibase. It's Java though.
Yes, you can write your migration scripts in the SQL variant of your choice.
It is well-tested and used by a lot of people.