Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

What are the best practices for managing revisions to the database schema and configuration ?

what tools do you use ?

Michael

share

locked by HopelessN00b Dec 5 at 11:47

This question exists because it has historical significance, but it is not considered a good, on-topic question for this site, so please do not use it as evidence that you can ask similar questions here. This question and its answers are frozen and cannot be changed. More info: help center.

closed as primarily opinion-based by HopelessN00b Dec 5 at 11:46

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise.If this question can be reworded to fit the rules in the help center, please edit the question.

1  
Which dbms are you interested in? You might also consider asking this (or searching for it) on stackoverflow.com –  squillman May 27 '09 at 19:40
    
yes, this was asked on SO. –  Assaf Lavie May 27 '09 at 20:20

5 Answers 5

up vote 5 down vote accepted

There is a great series of articles by K. Scott Allen via Coding Horror: Get Your Database Under Version Control about database version control. They have a SQL Server focus, but it could be applicable to other environments.

  1. Three rules for database work
  2. The Baseline
  3. Change Scripts
  4. Views, Stored Procedures and the Like
  5. Branching and Merging
share

Instead of storing the actual DDL scripts, I source control a description of the schema metadata that can be diffed against another database. I use SchemaCrawler - a free, commandline, open-source tool I wrote in Java, and designed for this purpose.

share

i acquired a VBS script that uses SQL-DMO to script SQL Server's objects. It then uses SourceSafe integration to check them into source-control. Run it nightly and you have historical DDL.

C:\WINNT\system32\cscript.exe vssAgent.vbs theServerName

vssAgent.vbs (Link 1, Link 2, Link 3)

You'll have to customize some login/passwords in the vbs to match your VSS credentials.

share

The big thing for controlling this is to limit permissions and have a choke point through which deployment changes go. You can use a VCS system with a database, just be sure people check script files in and out. Even if they use a tool to make changes, they should get the script used to actually make those changes (SQL Server includes this capability, not sure about other platforms).

Save the script files like any other file for your app in a VCS.

share

I'm afraid I like the old school approach of using the native dbms tools to dump the schema to a text file and putting that in version control. I'm sure some of the fancy tools do a nice job, but my existing box of tools works just fine for my needs. I'd only consider the fancy stuff if I needed automated migration or something like that. YMMV.

share

Not the answer you're looking for? Browse other questions tagged or ask your own question.