How to use VSS for Database management same as for source code?

Question

Hi All,

Thanks in Advance..

I want to put a question to solve my problem, the question is that how is the way to use database (Trigger, Sps, and other elements) in VSS same like using source code in VSS and access in Microsoft Visual Studio.

like doing check in and out of database elements in VSS or other tool for making database in central location and also conversioning of database elements for example version history of Store procedures.

it will be very helpful for me for getting previous version of any Store procedure if required. because there is alot of changes performing on store procedure and some time we do mistake and need the previous version of that store procedure. same like on VSS we can get the previous version of any source code file if we did any mistake.

Answer 1

If you are using VS, the easiest way to control your source objects is to create a Database project using the "Database" project template in Visual Studio.

The entire database project can be associated with source control (VSS in your case) and then all your DB object scripts are versioned.

A very important point to note is to make sure that Developers get out of their old habits of directly updating / changing objects in the DB because doing this will not stop them from doing so. An easy way out of that dilemma is to ensure that the DB project is built and deployed periodically(Continuous integration) just like your code is. This will ensure that if changes are directly done to DB, they will be lost and hence automatically inculcate the behavioural change in developers.

Refer link for a step by step tutorial as to how to get started using a DB project. Screen shots are for VS2010 but DB projects have been around since VS 2005 and more or less on the same lines. Very easy to use.

Answer 2

If you're willing to use Subversion or TFS, and are using SSMS to make your database changes, Red Gate's SQL Source Control maybe a tool that would work for you.

http://www.red-gate.com/products/SQL_Source_Control/