如何对 SQL Server 数据库进行版本控制?

2021-12-05 00:00:00 database svn sql-server version-control

我想让我的数据库受到版本控制.

I want to get my databases under version control.

我总是希望至少有一些数据(如alumb提到:用户类型和管理员).我还经常需要大量生成的测试数据用于性能测量.

I'll always want to have at least some data in there (as alumb mentions: user types and administrators). I'll also often want a large collection of generated test data for performance measurements.

如何将版本控制应用于我的数据库?

How would I apply version control to my database?

推荐答案

Martin Fowler 写了我最喜欢的关于这个主题的文章，http://martinfowler.com/articles/evodb.html.我选择不按照alumb 和其他人的建议将模式转储置于版本控制之下，因为我想要一种简单的方法来升级我的生产数据库.

Martin Fowler wrote my favorite article on the subject, http://martinfowler.com/articles/evodb.html. I choose not to put schema dumps in under version control as alumb and others suggest because I want an easy way to upgrade my production database.

对于我将拥有单个生产数据库实例的 Web 应用程序，我使用两种技术:

For a web application where I'll have a single production database instance, I use two techniques:

一个序列数据库升级脚本，其中包含将模式从版本 N 移动到 N+1 所需的 DDL.(这些进入你的版本控制系统.)一个 _version_history_ 表，类似于

A sequence database upgrade scripts that contain the DDL necessary to move the schema from version N to N+1. (These go in your version control system.) A _version_history_ table, something like

create table VersionHistory (
    Version int primary key,
    UpgradeStart datetime not null,
    UpgradeEnd datetime
    );

每次运行与新版本对应的升级脚本时都会获得一个新条目.

gets a new entry every time an upgrade script runs which corresponds to the new version.

这确保可以轻松查看存在的数据库架构版本，并且数据库升级脚本仅运行一次.同样，这些是不是数据库转储.相反，每个脚本都代表从一个版本移动到下一个版本所需的更改.它们是您应用于生产数据库以升级"它的脚本.

This ensures that it's easy to see what version of the database schema exists and that database upgrade scripts are run only once. Again, these are not database dumps. Rather, each script represents the changes necessary to move from one version to the next. They're the script that you apply to your production database to "upgrade" it.

  1. 用于备份、清理和缩小生产数据库的脚本.每次升级到生产数据库后运行此程序.
  2. 用于在开发人员的工作站上恢复(并在必要时调整)备份的脚本.每个开发人员在每次升级到生产数据库后都会运行此脚本.

警告:我的自动化测试针对模式正确但为空的数据库运行，因此该建议不能完全满足您的需求.

相关文章