Saving database schema in version control
thomask
Posts: 15
Hi there - I was wondering if there is any product/solution from Red Gate that can do the following:
1) Saving a database as a set of .sql files - one for each object.
2) Storing the database in version control system such as subversion.
3) Creating a blank database from these .sql files.
4) Automatically comparing two versions of a database using SQL Compare and generate an upgrade script.
It seems to me that Red Gate supports number 4) whereas there is no help with the other three.
The problem I am trying to solve is to have versioning on the database schema and to be able to detect conflicts in schema and stored procedures, when more than one developer is working on the database.
I could probably solve this myself using various tools from the net, but if someone already knows a good solution I'd prefer to use that instead - commerical or not.
Cheers,
Thomas
1) Saving a database as a set of .sql files - one for each object.
2) Storing the database in version control system such as subversion.
3) Creating a blank database from these .sql files.
4) Automatically comparing two versions of a database using SQL Compare and generate an upgrade script.
It seems to me that Red Gate supports number 4) whereas there is no help with the other three.
The problem I am trying to solve is to have versioning on the database schema and to be able to detect conflicts in schema and stored procedures, when more than one developer is working on the database.
I could probably solve this myself using various tools from the net, but if someone already knows a good solution I'd prefer to use that instead - commerical or not.
Cheers,
Thomas
Comments
Storing the resulting database in an edit-merge-commit source control system should be as straightforwards as adding the resulting directory with your usual source control tools. We don't currently have any particular special way of interfacing with these kinds of source control because we couldn't really see how we could be helpful there - if you have any ideas as to the kind of interface you'd want to see for step 2), we're still investigating this area so please do tell us what you'd like to see.
If you're using check out - edit - check in style source control, SQL Compare has a helper program called SQL ChangeSet which works with source control systems that support MSSCCI to automatically get the latest versions of files before comparison and check out the relevant files before a synchronization, and provides an interface for checking them back in afterwards. But as you're using Subversion that probably isn't relevant for you.
SQL Compare doesn't create databases, but if you create a completely blank, new database (i.e. just using CREATE DATABASE) you can synchronize all the structure elements into the database using SQL Compare.
I hope that's a helpful overview - please ask if anything wasn't clear or I misinterpreted what you were looking for.
Redgate Software
This is very interesting, I didn't know what SQL Changeset did.
I only have a license for SQL Compare in the standard edition so I cannot try it out.
Is it possible to get a temporary upgrade to SQL Compare?
Cheers,
Thomas
Redgate Software
Cheers,
Thomas
Bear in mind that if you are using Subversion, SQL Changeset is not a requirement, as there is no need for your working folder files to be checked out prior to modification by SQL Compare. SQL Changeset is useful for VSS-style systems.
You might find the following article interesting. It describes how SQL Compare 6 Pro can be used in combination with subversion.
http://www.sqlservercentral.com/articles/sql+tools/61769/
There is also a Red Gate white paper on database development approaches that may give you a few ideas:
http://www.red-gate.com/products/sql_compare/technical_papers/improved_database_development.pdf
David Atkinson
Red Gate Software
Product Manager
Redgate Software