版本控制SQL Server数据库 [英] Versioning SQL Server database

问题描述

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

我总是想在其中至少有部分数据 alumb 提及：用户类型和管理员）。我也经常想要大量收集的生成的测试数据用于性能测量。

解决方案

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

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

数据库升级脚本

序列数据库升级脚本，包含将模式从版本N移动到N + 1所需的DDL。 （这些在你的版本控制系统中。）_version_history_表，像

  create table VersionHistory主键，
 UpgradeStart datetime not null，
 UpgradeEnd datetime 
）; 
  

每次升级脚本运行时都会获得一个新条目，

这可以确保很容易查看数据库模式的版本是否存在以及数据库升级脚本只运行一次。同样，这些是不数据库转储。相反，每个脚本代表从一个版本移动到下一个版本所需的更改。

开发人员沙箱同步

1. 用于备份，清理和缩小生产数据库的脚本。


2. 在开发人员的工作站上还原（并在必要时调整备份）的脚本。每个开发人员在每次升级到生产数据库后运行此脚本。

注意：正确但空数据库，因此此建议不会完全适合您的需求。

I want to get my databases under version control. Does anyone have any advice or recommended articles to get me started?

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 measurement.

解决方案

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.

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

Database Upgrade Scripts

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.

Developer Sandbox Synchronization

1. A script to backup, sanitize, and shrink a production database. Run this after each upgrade to the production DB.
2. A script to restore (and tweak, if necessary) the backup on a developer's workstation. Each developer runs this script after each upgrade to the production DB.

A caveat: My automated tests run against a schema-correct but empty database, so this advice will not perfectly suit your needs.

这篇关于版本控制SQL Server数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！