版本控制 T-SQL 的最佳方法？

Best way to version control T-SQL?

提问by

Possible Duplicate:
Stored procedures/DB schema in source control

可能的重复：
源代码管理中的存储过程/数据库架构

What's the best way to version control my tables, views, sprocs, etc? Preferably automated or at least semi-automated :)

对我的表、视图、sproc 等进行版本控制的最佳方法是什么？最好是自动化的或至少是半自动化的 :)

Thanks

谢谢

回答by Dana

I asked this one yesterday and got some nice responses:

我昨天问了这个问题，得到了一些不错的回答：

Stored procedures/DB schema in source control

源代码管理中的存储过程/数据库架构

回答by Joe

The articles from K Scott Allen say it all: http://odetocode.com/Blogs/scott/archive/2008/01/31/11710.aspx

K Scott Allen 的文章说明了一切：http: //odetocode.com/Blogs/scott/archive/2008/01/31/11710.aspx

回答by Mario

I use Visual Studio 2008 Pro create Database projects (Other project types -> Database). We already use SVN as a code repository, so a project with a bunch of .sql files representing your stored procedures is just another thing to put in the repository - you can see diffs/history etc. This works the same with VSS or any other repository you use.

我使用 Visual Studio 2008 Pro 创建数据库项目（其他项目类型 -> 数据库）。我们已经将 SVN 用作代码存储库，因此带有一堆代表存储过程的 .sql 文件的项目只是放入存储库的另一件事 - 您可以看到差异/历史记录等。这与 VSS 或任何其他工作相同您使用的存储库。

The nice thing about Database projects is that your project will remember your connection string, and all you have to do is right click on a .sql file (or select all of them at once!) and select run to update it in the db. This makes it easy to update your .sql files from the repository and run them all to update all your stored procedures, verifying your database is updated in seconds.

数据库项目的好处是您的项目会记住您的连接字符串，您所要做的就是右键单击一个 .sql 文件（或一次选择所有文件！）并选择运行以在数据库中更新它。这使得从存储库更新 .sql 文件并运行它们以更新所有存储过程变得容易，验证您的数据库在几秒钟内更新。

You can also select create a LINQ project (Visual C# -> Database) and store all your LINQ code in your repository.

您还可以选择创建 LINQ 项目（Visual C# -> 数据库）并将所有 LINQ 代码存储在您的存储库中。

Hope that helps!

希望有帮助！

回答by Aeon

Write migration scripts for all db changes and keep them in a repository. Enforce a policy of making all changes to the db only by running a script; that way there is a record of what has been done, and a way to revert it. Investigate whether there's a migrations framework available for your favorite language/db combination.

为所有数据库更改编写迁移脚本并将它们保存在存储库中。强制执行仅通过运行脚本对数据库进行所有更改的策略；这样就有了已经完成的记录，以及恢复它的方法。调查是否有适用于您最喜欢的语言/数据库组合的迁移框架。

回答by Cervo

If you were super lazy you could use the SMO (SQL Server Management Objects) or if using SQL Server prior to 2005 the DMO (distributed managmeent objects) to script out all tables/views/stored procedures daily and then compare the script to the script in source control and if there are any changes check the new version in. You won't be able to necessarily have as pretty of a script as if you just created all db changes in scripts, but at least you can recreate all tables/stored procedures/views. For example, in my table creation scripts there are often comments.

如果您非常懒惰，则可以使用 SMO（SQL Server 管理对象），或者如果使用 2005 年之前的 SQL Server，则使用 DMO（分布式管理对象）每天编写所有表/视图/存储过程的脚本，然后将脚本与脚本进行比较在源代码管理中，如果有任何更改，请检查新版本。您不一定能够像刚刚在脚本中创建所有数据库更改一样漂亮的脚本，但至少您可以重新创建所有表/存储程序/意见。例如，在我的表创建脚本中经常有注释。

Here is an article to get you started on scripting: http://www.sqlteam.com/article/scripting-database-objects-using-smo-updated.

这是一篇让您开始编写脚本的文章：http: //www.sqlteam.com/article/scripting-database-objects-using-smo-updated。

Again, this is mainly if you are too lazy to bother with version control and it won't help if you change something twice in one day. Also any data migration scripts still have to be saved and checked in because this won't pick up ad hoc SQL, only database objects.

同样，这主要是因为您懒得理会版本控制，如果一天内更改两次，也无济于事。此外，任何数据迁移脚本仍然需要保存和签入，因为这不会选择即席 SQL，只会获取数据库对象。

回答by Keith Bloom

I'm using Visual Studio Database edition which can export the schema from SQL Server in to a Visual Studio project. This is then stored in Source Control and can be deployed where ever needed. The VS Database project is just a bunch of scripts though and it's a clunky way of working.

我正在使用 Visual Studio 数据库版本，它可以将架构从 SQL Server 导出到 Visual Studio 项目中。然后将其存储在源代码管理中，并可以部署在需要的地方。VS 数据库项目只是一堆脚本，它是一种笨拙的工作方式。

A more robust method would be to use a database migration framework and if you're working with .Net check out this blog post for a good description http://flux88.com/NETDatabaseMigrationToolRoundup.aspx.

更可靠的方法是使用数据库迁移框架，如果您正在使用 .Net，请查看这篇博客文章以获得很好的描述http://flux88.com/NETDatabaseMigrationToolRoundup.aspx。

Update

更新

As mentioned in the comments, this page is no more. So here is the last known snapshot from wayback machine http://web.archive.org/web/20080828232742/http://flux88.com/NETDatabaseMigrationToolRoundup.aspx

如评论中所述，此页面已不复存在。所以这是来自回程机器的最后一个已知快照http://web.archive.org/web/20080828232742/http://flux88.com/NETDatabaseMigrationToolRoundup.aspx

回答by Daniel Zr

Try Randolph, One of the best SQL Version control tools I know.

试试Randolph，这是我所知道的最好的 SQL 版本控制工具之一。

回答by ViSu

I have written a DDL trigger which logs all the changes done to the definition of SQL objects (triggers, tables, SP, view etc). I could very well invoke extended SP from the trigger and store the details in another Database and use that as repository. But if your team is really disciplined any source control should do the trick. The trigger is used as an audit mechanism and it's ideal for teams which are geographically scattered.

我编写了一个 DDL 触发器，它记录对 SQL 对象（触发器、表、SP、视图等）的定义所做的所有更改。我可以很好地从触发器调用扩展 SP 并将详细信息存储在另一个数据库中并将其用作存储库。但是，如果您的团队真的纪律严明，则任何源代码控制都可以解决问题。触发器用作审计机制，非常适合地理位置分散的团队。