Posted by Erland Sommarskog on 01/29/07 23:11

(d.grabov@gmail.com) writes:
> We are trying to use source control to store our database objects and
> queries in Perforce. The general idea is to produce scripts which
> create all objects in the DB and then store the SQL in source control.

That's a very good idea!

> Has anyone done this before and can suggest some advice? Alternatively
> if anyone has other methods for using source control on a database,
> could you please let me know?

We have used Source Control for our SQL code for more than 10 years,
although we are using SourceSafe and not Perforce. Over the years, we
have developed some tools around this, which by the time have become
quite versatile. But as a start, BAT files and OSQL or SQLCMD can take
you a long way. Just don't forget the -I option to enable quoted
identifiers!

> Also, is there any automated way of extracting individual scripts for
> object creation from the database?

This can be done with DMO on SQL 2000 and SMO on SQL 2005. But once you
have you code under source control, you should not script from the
database. The database is the place where you deploy your binaries.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]