Introduction
If you are using a source control system to manage your project's source code, you have probably wondered if you could do the same with your database. You can! Here's my take at it: ScriptDB4Svn does what its name suggests: it scripts your database for usage in a source control system. Because I use SVN with the TortoiseSVN shell extensions, I've targeted this project to be used with SVN, but you can probably use it with other source control systems.
Please note that the tool relies upon the Scripting tool from Microsoft which was shipped with SQL Server 2000, and will only have been installed on your system if you chose for backward compatibility with SQL Server 7.0 during the installation. If you are missing the tool (scptxfr.exe) it can be found on the SQL Server 2000 installation CD.
The tool was tested on SQL Server 2000 only. I don't know if it works with SQL Server 2005.
What does ScriptDB4Svn do exactly?
The tool scripts Microsoft SQL Server tables, relationships, views, stored procedures, user defined types, defaults, rules, triggers, functions and data into individual .sql files. Those individual files can then be added to your source control system and become available for versioning. The tool can also be used to automatically create a clean database from generated scripts, which can be very useful for integration in any tools like NAnt, MSBuild, CC.net etc.
What are the benefits?
There are numerous benefits; these are just the ones I came up with. If you have any additions, let me know!
- You are aware of other developer’s database changes and they are aware of yours. Your databases will remain in-sync with each other.
- See who added what table, column, trigger, or broke your stored procedure. (‘SVN Blame’ shows what was added/changed when and by whom)
- Create clean databases from generated scripts for easy deployments to test and production environments.
- Add database script execution to continuous integration scripts.
- If you lose anything in your database, you have multiple scripted backups so you can easily restore it.
- Tagging: When releasing a new version of your program, include the exact version of the database and its data within the same SVN Tag / branch!
Working with ScriptDB4Svn - Manual
Put a copy of ScriptDB4SVN.exe and its .config file in the folder where you want your database scripted. Open the .config file and make proper changes to it. The file contains comments that will tell you what the different settings are for.
If you are going to use the tool in your development team, I recommend you to have everyone in your team read this article. I will save you some headaches!
How to manage database changes
- If any project-member makes a change to anything in their version of the database, they simply run the scripting tool to regenerate the scripts, and then commit the changes to the SVN repository.
- Upon executing an SVN Update command, any updated scripts are copied to other team members’ local versions. Once they run the scripting tool again, the database changes are reflected in Windows Explorer by TortoiseSVN. By viewing the TortoiseSVN Diff view of the table/view/whatever, the exact changes made by the developer become visible.
- The other team members make the appropriate changes to their local databases, either by executing the new script, or by making the changes manually in SQL Server.
- Once the tool generates scripts identical to the ones in the repository, the databases are in-sync again!
A paradoxical thing about this approach is that changes made by other developers only become visible after the scripts are regenerated locally. This way, it appears that the remote database changes have been ‘undone’ locally, while in fact the remote changes haven’t been processed (copied) yet. Any –uncommitted– changes a developer has made to their local database, will appear in the same way remote developers’ –unprocessed– changes appear in Windows Explorer.
Handling/resolving data differences
Resolving data conflict can be a pain because of relationships that assure the relational integrity of the data is maintained. If the data in more than one table has changed, simply executing a delete statement or inserting data can cause errors. In these situations it is quite useful to remove those constraints, update the data and then recreate the relationships. When done, the dropped constraints can easily be re-created from the Relationship scripts. Follow these steps to avoid annoyances:
- SVN Revert your version of the data script to the HEAD ('their') revision.
- Open the script in Query Analyzer, connect to the right database
- Execute the script
- If step 3 generated an error, find out what constraint caused the error, above the
DELETE FROM the_current_table line, add: ALTER TABLE table_that_contains_the_constraint DROP CONSTRAINT FK_the_name_of_the_annoying_constraint, now try step 3 again. Repeat this until the script executes without errors.
- Repeat steps 1 to 4 until all data changes have been made.
- Rescript your database. If you dropped any constraints in step 3, recreate them by executing the appropriate relationship scripts (SVN Revert the file, strip the lines for constraints that still exist, execute)
Important note: Watch out for relationships that have CASCADE DELETE's enabled! Deleting data in a source controlled table could then result in the loss of data of other -possibly not source controlled- tables!!
Handling/resolving minor script differences
There are a couple of situations where differences in generated scripts can occur, when they aren’t really different. Here’s how you can resolve them:
- Index/PK names: To resolve a difference in the name of a Primary key or index, in SQL Query Analyzer, execute:
sp_rename 'myname', 'theirname'
- CASE differences in table & column names: execute
sp_rename 'dbo.Table.someColumn', 'SomeColumn'
- CASE differences in
CREATE statements: for some odd reason SQL Server remembers the case of the CREATE statement when it was executed. For any other object than tables, SVN Revert your version of the .sql file to the HEAD revision, open the file in Query Analyzer and execute it.
- COLLATION differences: these can be a real pain. If the developers in a team are using different collation settings in SQL Server, any textual columns like
varchar and text will contain different collations. Make sure to all use the same collation settings! If you aren’t bothered by different collation settings, you can optionally turn “IgnoreCollation” on in the .config file of the tool.
Handling conflicts
Sometimes conflicts can occur in the script files. Usually this is the result of not committing the generated scripts after local database changes have been made. The best way to resolve conflicts is:
- Regenerate your scripts, any conflict files will be removed automatically.
- Now, check the Diff and make any pending changes to your database.
- Regenerate the scripts again and commit your file.
Best practices
Follow these guidelines to prevent yourself from getting into problems.
- Commit local database changes to the SVN repository as soon as possible, but only if any programming code that relies upon the DB change is committed at the same time. If the change can break code other developers are using, provide code that fixes those problems within the same commit.
- Schedule an automatic SVN Update, and the script tool to be executed at least once a day so you will notice committed database changes as soon as possible. 9 AM is a nice time for this: new day, new scripts.
- Process other users’ database changes immediately when you notice them. Waiting to do so will make your life hard.
- Put a copy of the ScriptDB4SVN.exe in the folder that contains the scripts and Add it to the repository. If an updated version becomes available, you can simply overwrite it with the new version. Other developers automatically take over the new version upon SVN Update.
- If you deleted a table/view/anything from your database, don’t remove the corresponding script file! The contents of the script file will automatically be cleared by the tool. This way the deletion will be noticed by other developers. Once -everybody- has deleted the item, the file can be removed from SVN.
History
- October 8, 2006: Original article posted.
