Take the 2-minute tour ×

In the scope of responsible programming and versioning, I would like to start to version my database changes especially since I am developing on my database instance then moving it to production. I haven't found any thing that truly makes sense to me on how to do this. I am using Visual Studio 2010 Pro as my IDE. Is there a document that makes this process simple and able to detect changes to the database with relative ease? Or what should I change in my workflow to make this easier?

share|improve this question
add comment

3 Answers

up vote 5 down vote accepted

One way that I've successfully done this sort of thing in the past, is via Sql Source Control. Visual Studio does not offer this functionality for you.

Alternatively, you can use SSMS to generate the Database scripts for you and save it as a file; then you can check in the script. You would chose whether you generate the whole DB script in one file or whether you do it on an object by object basis. The syncing part will have to be done by you by executing your scripts in production. In conclusion a total nightmare.

Redgate also offers Sql Compare, which is great for syncing databases. Take a look at their products if you or your company can afford them.

share|improve this answer
Ooh... I like the look of that package. –  Mike Wills Jan 25 '12 at 16:32
In VS2010 - you can drag objects from the Server Explorer to the Project Explorer to script DB objects. –  Lucero Jan 25 '12 at 20:53
add comment

We use our own DB solution in-house which brings all the tools required for proper DB versioning. While I realize that it may not be a perfect solution for everyone, I invite you to have a look at it (it is open-source): bsn ModuleStore

The versioning aspect is as follows: the tool can script out the SQL semi-automatically, and it does reformat the source code to be in an uniform format. The files will therefore always be identical for the same source, no matter of when and by whom something has been scripted; this therefore works nicely with non-locking source control systems (especially SVN, Git or Mercurial).

The reformat puts all statements in the same form (e.g. optional keywords such as AS, INNER, OUTER etc. are dealt with), scripts everything to the "dbo" schema (even if it was in a different one), puts all identifiers into the square braces ([something]), uppercases all reserved words, does the indentation etc.

Besides versioning, the runtime part of the tool can diff the running DB and the CREATE scripts (DB source code) and apply updates automatically for all non-destructive changes (e.g. updating indexes, constraints, views, stored procedures, triggers, custom types, new tables etc.). Destructuve changes have to be scriped manually (table changes which then usually require data transformations). The runtime will make sure that all updates are performed in a transaction and rollback if the resulting DB doesn't match the CREATE scripts, therefore you get the safety of knowing that the DB is exactly on the version required by the application, even if it has been tampered with manually.

Also, multiple "modules" can be used in a single database. Each module is stored as a schema and independent of other schemas, thereby making it possible to add or remove modules from one single DB, and avoiding the need to create multiple databases for different parts of the application. Also, the use of schemas to do this makes sure that there are no name collisions.

It may be worth noting that the toolset has no dependency to the SMO, it is autonomous.

share|improve this answer
add comment

Save Your Database scripts at SVN. Here is the Refernce How to use SVN Tortoise


Save your database script at VSS. Here is the reference What is VSS ? How can we use that ?

In both cases you can keep track of the changes done so that in future you can check the history which in saved in the form of versions.

You can use Red Gate product also


How do you pull out what what has changed?

Use comparison feature to check the changes made in the previous versions.

How do I apply the changes to the live database server?

Download the latest file from server.

I hope you are not using the Drop statements for the Table in your consolidated script. As it will delete all records from the table.

Drop statements will take place for Stored Pro, View, Function etc.

Please note that you have to run the complete latest database script file on the production server with below mentioned action plans

1. Remove Drop Statement for Schema DDL
2. Add Drop/Create Statements for Stored Proc/Views
3. Include Alter statements DML of schema.

Hope this will definitely help you.

enter image description here

share|improve this answer
This doesn't really answer the question. I know how to use SVN, but I don't know how to version the database. –  Mike Wills Jan 25 '12 at 17:34
Kindly read the answer again. It is mentioned that these products create history of the complete database/Database individual script. The point to note down here is that you need to save the complete database script in either of the product so next time you can compare the changes if needed. I hope this clarifies to much greater extend now :) –  abcdefghi Jan 25 '12 at 17:39
I know how to use SVN and neither article mentions anything about integration with SQL server and showing differences. Your solution is to just pull a new script? How do you pull out what what has changed? How do I apply the changes to the live database server? –  Mike Wills Jan 25 '12 at 17:49
You can check the edit of my answer now. –  abcdefghi Jan 25 '12 at 17:58
Okay, so what you are saying is to manually create the script. I know that automatic creation isn't always reliable, but that is preferred for a starting point if possible. We haven't done anything like this in the past. –  Mike Wills Jan 25 '12 at 19:40
add comment

Your Answer


