[sql-server] Versioning SQL Server database

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.

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to database

Implement specialization in ER diagram phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' cannot be loaded Room - Schema export directory is not provided to the annotation processor so we cannot export the schema SQL Query Where Date = Today Minus 7 Days MySQL Error: : 'Access denied for user 'root'@'localhost' SQL Server date format yyyymmdd How to create a foreign key in phpmyadmin WooCommerce: Finding the products in database TypeError: tuple indices must be integers, not str

Examples related to svn

Error "can't use subversion command line client : svn" when opening android project checked out from svn How to view changes made to files on a certain revision in Subversion Intellij idea subversion checkout error: `Cannot run program "svn"` How change default SVN username and password to commit changes? How to rename a file using svn? Connect Android Studio with SVN svn: E155004: ..(path of resource).. is already locked SVN Commit failed, access forbidden How to add an existing folder with files to SVN? Update OpenSSL on OS X with Homebrew

Examples related to version-control

How can I switch to another branch in git? Do I commit the package-lock.json file created by npm 5? Project vs Repository in GitHub Remove a modified file from pull request Git push: "fatal 'origin' does not appear to be a git repository - fatal Could not read from remote repository." Git: How to squash all commits on branch git: updates were rejected because the remote contains work that you do not have locally Sourcetree - undo unpushed commits Cannot checkout, file is unmerged Git diff between current branch and master but not including unmerged master commits