Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What is a good way to migrate DB changes from Development to QA to Production environments? Currently we:

  1. Script the change in a sql file and attach that to a TFS work item.
  2. The work is peer-reviewed
  3. When the work is ready for testing it the sql is run in QA.
  4. When the work is ready for production it the sql is run in the production databases.

The problem with this is that it is very manual. It relies on the developer remembering to attach the sql or the peer-reviewer catching it if the developer forgets. Sometimes it ends up being the tester or QA deployer who discovers the problem.

A secondary problem is that you sometimes end up needing to manually coordinate changes if two seperate tasks change the same database object. This may just be the way it is but it still seems like there should be some automated way of "flagging" these issues or something.

Our setup: Our development shop is full of developers with a lot of DB experience. Our projects are very DB oriented. We are mainly a .NET and MS SQL shop. Currently we are using MS TFS Work Items to track our work. This is handy for code changes because it links the changesets to the work items so I can find out exactly what changes I need to include when migrating to QA and Production environments. We are not currently using a DB project but may switch to that in the future (maybe that is part of the answer).

I am very used to my source control system taking care of things like this for me and would like to have the same thing for my SQL.

share|improve this question
sounds like a good open source project (if one already doesn't exist) – Patrick Jan 3 '11 at 23:48
@Patrick... yes it does but it seems like there would be a way to do this with all the MS functionality. I'd like a OS for home projects too but for work it would be nice to stay within the development environment we have. – Beth Whitezel Jan 4 '11 at 0:05
I think database projects are good for this. They can be source controlled and change scripts can be created based on whats in the source. – mrskaggs Jan 4 '11 at 0:50
@mrskaggs do they act kind of like code changesets? That's exciting if they do. (and you should answer with that) – Beth Whitezel Jan 4 '11 at 0:51

5 Answers 5

up vote 13 down vote accepted

In a VS environment, I've always used database projects to implement the update scripts. I tend to use unimaginative names like "DatabaseUpdate17.sql" or "PriceUpdateFebruary2010.sql" for my scripts. Having them as database projects lets me tie them to Team Server tasks, bugs (and if we did code reviews, to them as well). I also include in each database (that I have authority over) a table specifically for the collection of changes to the schema.

CREATE TABLE [dbo].[AuditDDL](
    [EventData] [xml] NULL,                    -- what did they do
    [EventUser] varchar(100) NOT NULL,         -- who did it
    [EventTime] [datetime] DEFAULT (getdate()) -- when did they do it

Well, that takes care of 3 of the 6 Ws.

INSERT INTO AuditDDL(EventData, EventUser)
SELECT EVENTDATA(), original_login()

I include an insert statement to log the beginning of a patch as well as the end of a patch. Events happening outside of patches are things to look into.

For example, a "begin patch" insert for "patch 17" would look like:

INSERT INTO [dbo].[AuditDDL]
           ('<EVENT_INSTANCE><EventType>BEGIN PATCH 17</EventType></EVENT_INSTANCE>'

Since it also catches when indices are rebuilt, you'll need to run the following every month or so to clear out those events:

WHERE [EventData].exist('/EVENT_INSTANCE/EventType/text()[fn:contains(.,"ALTER_INDEX")]') =1

WHERE [EventData].exist('/EVENT_INSTANCE/EventType/text()[fn:contains(.,"UPDATE_STATISTICS")]') =1

Earlier version previously posted on Server Fault.

In a SOX and PCI-DSS compliant environment, you will never have access to the production servers. Therefore the scripts need to be clear and exercised beforehand. The comments at the top of the update scripts include lists of new tables, stored procs, functions, etc as well as lists of modified tables, stored procs, functions, etc. If data gets modified, explain what is being modified and why.

A secondary problem is that you sometimes end up needing to manually coordinate changes if two separate tasks change the same database object. This may just be the way it is but it still seems like there should be some automated way of "flagging" these issues or something.

I've never come across a tool that lets us track this automatically. Previous employers used a principle of "database owner" - one and only one person who is personally in charge of the database. This person won't be the only developer working against that database, but rather all changes have to go through them. This has worked reasonably well to keep changes from colliding and damaging each other.

share|improve this answer
So you do this in VS and not SSMS right? I'm trying to figure out the best way to do SCM for my database work right now, and we use Hg. – jcolebrand Jan 5 '11 at 5:03
@jcolebrand, yes, I use VS to write and keep track of the scripts. The production staff use SSMS to run the scripts to update the production databases. The database tools inside VS are pretty decent for comparing schemas. RedGate's SQL Compare is a decent alternative. – Tangurena Jan 5 '11 at 13:46

Have you looked at SQL Source Control? You can use it to connect your SQL Server to TFS/SVN/Vault or VSS -

share|improve this answer
Thanks, that is one I've looked at little a bit. If we don't like how the db projects work in VS then red-gate sounds like a good solution. – Beth Whitezel Jan 11 '11 at 18:04

Another solution is to use something like PowerDesigner, ERWin, etc to design and manage changes to your database.

We're starting to transition to a policy where databases are modeled in PowerDesigner. All changes to the database structure/code is done in the model, checked into source control and then change scripts are generated from the models to implement the changes in the database. These change scripts are also checked in to source control. Large changes are peer reviewed and PowerDesigner makes this very easy using built-in features.

PowerDesigner is a generic modeling tool supporting more than just databases so we're starting to use it to manage requirements, create conceptual, physical and architecture diagrams (OOM's too), etc. Basically, we're using it to provide the backbone to our software engineering process.

(I'm in no way affiliated with Sybase, who developed PowerDesigner - just thought I'd throw that in there).

share|improve this answer
Thanks, I'll take a look at that one too. – Beth Whitezel Jan 5 '11 at 16:33

I know it sounds overkill to most DBAs:

Have you considered using Ruby on Rails to track the Database changes (and only the DB changes). You don't need to run any app or write any ruby code etc. But I found the style of migrations (that's how they call it) is quite useful:

Sql Server is also supported, you might have to use JRuby + JDBC though.

share|improve this answer
haven't looked at it yet. Thanks I'll take a look. – Beth Whitezel Jan 4 '11 at 4:54

DB Ghost

DB Ghost is my favorite tool for managing databases.


  1. All of the objects in your database are stored as scripts in source control.
  2. You can script 'static data' (lookup table data) too.
  3. You can update source control manually or by scripting a 'model' development database.
  4. You can build a database (quickly) from the scripts in source control (including static data).
  5. You can deploy changes to instances of the database, including any production instances:
    • You can compare a 'build database' (created from the scripts) to an existing database and generate a change script.
    • You can direct DB Ghost to automatically sync changes between two instances of the database, e.g. a build database and your production database.

[4] is particularly handy for making local changes or creating separate instances for different environments. In fact it's so easy that I create a separate database for every feature or bug I work on that impacts a database.


The main advantage of using it over maintaining explicit change or migration scripts is that you mostly don't need to maintain explicit change or migration scripts – you can mostly maintain just the 'current version' of your database. One annoying aspect of managing migration scripts is that there's no easy way to see, e.g. a list of columns in a table (based on the migration scripts). Of course some changes need to be made as explicit migrations, but they're easy enough to handle as separate scripts.

A particularly nice consequence of being able to manage databases as (a set) of scripts and also being able to quickly create new instances is that unit testing important database code is very easy (and pretty fun too). I use tSQLt for unit testing.

I only wish there was a similar tool for other DBMS-s.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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