Storing SQL Server 2005 objects and queries in Perforce

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

d.grabov@gmail.com - 29 Jan 2007 11:48 GMT

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.
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?

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

Reply to this Message

Erland Sommarskog - 29 Jan 2007 23:11 GMT

> 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.

Signature

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

Reply to this Message

Mork69 - 30 Jan 2007 09:24 GMT

As Erland says, it's a very good idea to store your SQL object under
source control. We have written tools to make the process of keeping
your changes under source control and propagating them to your test
and production environments simple and straightforward. Please see
www.dbghost.com for more details.

Some great advantages of this way of doing things are:

1. A 100% audit trail of who changed what, when and why (source
control history)
2. Scales to hundreds or even thousands of developers
3. Saves developer time writing upgrade or delta scripts - the tool
does it for you
4. SQL changes can be properly synchronized with the other code
changes via changelists (atomic checkin)
5. Branches are easily compared for differences when you are comparing
the object CREATE statements rather than hand coded ALTER scripts.

I hope you like our approach :)

Regards,

Malcolm

On Jan 29, 11:48 am, d.gra...@gmail.com wrote:

> We are trying to use source control to store our database objects and
> queries in Perforce. The general idea is to produce scripts which
[quoted text clipped - 5 lines]
> Also, is there any automated way of extracting individual scripts for
> object creation from the database?

Reply to this Message