How to use VSS (or other system) for Database management and version control?

What is the best way to version database objects (Trigger, SPs, and other elements) in VSS in 开发者_如何转开发a similar fashion to the way that we store source code in VSS and access it in Microsoft Visual Studio?

We would like to check database elements in and out in VSS or a similar tool so that we can store database objects in a central location, and also so that we can have versioning of database elements, for example, version history of stored procedures.

We currently use SQL Server 2005 as our database engine.

---

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.

---

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/

[Edit] We've now added VSS and SourceGear Vault support. Try the early access build: http://www.red-gate.com/MessageBoard/viewtopic.php?t=12265