Sql server database versioning with subversion (svn) – codeproject

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. Raid 0 data recovery 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. 7 data recovery serial key 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. Database modeling If you are missing the tool ( scptxfr.exe) it can be found on the SQL Server 2000 installation CD.

The tool scripts Microsoft SQL Server tables, relationships, views, stored procedures, user defined types, defaults, rules, triggers, functions and data into individual .sql files. A database record is an entry that contains Those individual files can then be added to your source control system and become available for versioning. Java database 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. Easeus data recovery 9 serial What are the benefits?

• 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!

Put a copy of ScriptDB4SVN.exe and its .config file in the folder where you want your database scripted. Iphone 5 data recovery software free Open the .config file and make proper changes to it. Database partitioning 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. Database gif 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. Database project ideas Once they run the scripting tool again, the database changes are reflected in Windows Explorer by TortoiseSVN. Iphone 5 data recovery without backup 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.

A paradoxical thing about this approach is that changes made by other developers only become visible after the scripts are regenerated locally. Bplan data recovery 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. Data recovery boston ma 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. Ipad 2 data recovery Handling/resolving data differences

Resolving data conflict can be a pain because of relationships that assure the relational integrity of the data is maintained. Data recovery company If the data in more than one table has changed, simply executing a delete statement or inserting data can cause errors. Data recovery network drive In these situations it is quite useful to remove those constraints, update the data and then recreate the relationships. Data recovery yelp When done, the dropped constraints can easily be re-created from the Relationship scripts. Database join table Follow these steps to avoid annoyances:

• 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. Google database Repeat this until the script executes without errors.

• Rescript your database. Database quizlet 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. Database synonym 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 CREATE statements: for some odd reason SQL Server remembers the case of the CREATE statement when it was executed. Database management jobs 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. Iphone 6 data recovery free 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. Data recovery ubuntu 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.

Sometimes conflicts can occur in the script files. Database diagram tool Usually this is the result of not committing the generated scripts after local database changes have been made. Database field types The best way to resolve conflicts is:

• 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. Data recovery best buy 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. Raid 5 data recovery 9 AM is a nice time for this: new day, new scripts.

• Put a copy of the ScriptDB4SVN.exe in the folder that contains the scripts and Add it to the repository. Database administrator salary nyc If an updated version becomes available, you can simply overwrite it with the new version. Database p 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. Cloud 9 database This way the deletion will be noticed by other developers. Data recovery on ssd Once -everybody- has deleted the item, the file can be removed from SVN.

This is a well written article and a well thought out methodology for a problem that plagues most IT shops and goes largely unnoticed. Database query tools However, I believe there are some fundamental issues with versioning SQL scripts. Database 2000 First, no matter how you swing it, there is always the problem that a developer checks in the wrong script or even forgets to check in entirely. Database business rules Therefore, the integrity of the SQL code is always in doubt. Iphone 5 data recovery Also, versioning SQL scripts often presents problems when attempting to automatically deploy changes in certain scenarios and especially rollback deployed changes. Database implementation Deployment issues crop up when there are complex dependencies. Raid 1 data recovery software Rollbacks are almost always near impossible without separate rollback scripts.

DBVS is an innovative product that versions not SQL scripts, but XML representations of each object. Seagate data recovery You don’t have the problem of developers forgetting to check in since changes are made directly in the development environment and a check in command causes the DBVS server to connect directly to that database to extract its metadata. Database report Therefore, you can use any means including SQL scripts or IDEs to alter the object. Data recovery specialist Once you’re finished, you check in and DBVS handles the rest. Data recovery iphone 6 As far as control, DBVS can deny ALTER permissions from all controlled objects until they are checked out. Database building Once checked out, only the user who performed the check out is granted ALTER permissions. Data recovery top 10 This methodology gives you 100% data integrity.

By versioning XML rather than SQL code, automated database deployments and rollbacks become possible. Database hosting DBVS compares the revisions of the database objects being deployed to the corresponding live objects in the target database and produces a list of differences. Data recovery best Those differences equate to changes that must be made in the target database to make those objects look like the object revisions being deployed. Data recovery program The list of changes is ordered to account for dependencies and translated into SQL code, which is then run against the target database. 7 data recovery 94fbr Rollbacks work in a similar fashion.

You can find out more at http://www.dbvsys.com. Database languages All comments, questions and suggestions are welcome. Database ranking I would be interested in hearing your feedback.

Unlike PVCS, SVN doesn’t use a locking mechanism for files managed. Data recovery youtube This is one of the great benefits of SVN. 911 database In the scenario of database versioning, this literally means you and one or more of your collegues can make changes to the same table at the same time, check in those changes and all have these changes merged back into their local databases seamlessly.

Your suggested solution would work great in a single database server development environment, with all developers working on that one database all at the same time. Data recovery download However, this tool provides a way to have developers independently work at local versions of their databases, and only check in/merge changes back into mainstream development once their changes have been successfully implemented. Data recovery after factory reset Just like they would do with their source code files.

banner