How to do version control for SQL Server database?

by

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.

More Related Contents:

  1. Updating image in VB and SQL Server not working [closed]
  2. Query to list number of records in each table in a database
  3. Storing a file in a database as opposed to the file system?
  4. Multiple Indexes vs Multi-Column Indexes
  5. Preferred Method of Storing Passwords In Database
  6. Using a sort order column in a database table
  7. Create a view with ORDER BY clause
  8. What does ‘COLLATE SQL_Latin1_General_CP1_CI_AS’ do?
  9. What columns generally make good indexes?
  10. How to determine total number of open/active connections in ms sql server 2005
  11. How to check if a database exists in SQL Server?
  12. Getting execute permission to xp_cmdshell
  13. TSQL: Create a view that accesses multiple databases
  14. How to migrate a PostgreSQL database into a SQLServer one?
  15. What is the point of “Initial Catalog” in a SQL Server connection string?
  16. “Cannot open user default database. Login failed.” after installing SQL Server Management Studio Express
  17. How to create a yes/no boolean field in SQL server?
  18. Best way to store time (hh:mm) in a database
  19. How can I run just the statement my cursor is on in SQL Server Management Studio?
  20. Executing set of SQL queries using batch file?
  21. Database design: Calculating the Account Balance
  22. In SQL Server, how can I lock a single row in a way similar to Oracle’s “SELECT FOR UPDATE WAIT”?
  23. Date range overlapping check constraint
  24. What are the advantages of VistaDB
  25. Difference between database and schema
  26. Generate table relationship diagram from existing schema (SQL Server) [closed]
  27. Calculate Time Difference Between Two Rows
  28. What are the limitations to SQL Server Compact? (Or – how does one choose a database to use on MS platforms?)
  29. How do you deal with blank spaces in column names in SQL Server?
  30. Tool for Scripting Table Data

Leave a Comment