Prev: batch file
Next: Organizing Solutions and Projects
From: SqlBeginner on 27 Jul 2010 20:12
Hi All,

I am using SQL Server 2008. Just want to know what is the best method to
maintain the DB objects in source control and reverting back to any previous
version with ease.

Any pointers on this wud be of great help.

Regards
Pradeep
From: Eric Isaacs on 28 Jul 2010 14:03
It depends on which kind of objects you're referring to. You cannot
maintain objects in source control, but you can maintain scripts of
objects in source control. Things like views, stored procedures,
functions, triggers, indexes, and synonyms can be maintained in source
control with scripts of those objects. Creation scripts of tables can
be maintained, but data cannot be maintained. Database backups are
better suited for that purpose.

In SSMS, just make sure you always script your changes. Get in the
habit of checking out the scripts from source control and editing
those scripts and checking them back in, rather than editing the
objects in SSMS directly. That will provide a better history of the
actual changes to the code. I would also suggest using a tool such as
Red-Gate's SQL Prompt to format your SQL into standard formats so that
the changes are easier to see when comparing differences between the
current version and the previous versions of the scripts.

-Eric Isaacs

From: SqlBeginner on 28 Jul 2010 18:35
Thanks for the response Eric.

Instead of manually taking the scripts and checking in and out each time is
there any tool which can be of use. Was thinking abt Team Foundation Server.
Is it a good idea to use it for SQL source control?

Is there any industry standards on SQL scripts maintanance.

Regards
Pradeep

"Eric Isaacs" wrote:

> It depends on which kind of objects you're referring to. You cannot
> maintain objects in source control, but you can maintain scripts of
> objects in source control. Things like views, stored procedures,
> functions, triggers, indexes, and synonyms can be maintained in source
> control with scripts of those objects. Creation scripts of tables can
> be maintained, but data cannot be maintained. Database backups are
> better suited for that purpose.
>
> In SSMS, just make sure you always script your changes. Get in the
> habit of checking out the scripts from source control and editing
> those scripts and checking them back in, rather than editing the
> objects in SSMS directly. That will provide a better history of the
> actual changes to the code. I would also suggest using a tool such as
> Red-Gate's SQL Prompt to format your SQL into standard formats so that
> the changes are easier to see when comparing differences between the
> current version and the previous versions of the scripts.
>
> -Eric Isaacs
>
> .
>
From: Erland Sommarskog on 29 Jul 2010 07:49
SqlBeginner (SqlBeginner(a)discussions.microsoft.com) writes:
> Instead of manually taking the scripts and checking in and out each time
> is there any tool which can be of use. Was thinking abt Team Foundation
> Server. Is it a good idea to use it for SQL source control?

TFS is a version control system which has some advantages and some new
thinking, but also in my opinion lacks some essential features. (But I've
only seen TFS 2005.)

Red Gate has a product SQL Source Control which may be of interest to you.
It is not a version-control system per se, but more a glue between SSMS and
some version-control system; at least so I understand it.

> Is there any industry standards on SQL scripts maintanance.

It is here I could add a link to
http://www.sommarskog.se/AbaPerls/index.html, but I would be grossly
lying if I said that this is an industry standard.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Eric Isaacs on 29 Jul 2010 17:35
I posted a reply earlier, but for some reason it didn't reach you.

I also recommended that you look at SQL Source Control from Red-Gate.
It's not foolproof as it's just an interface that makes it easier to
check out the script files. It also seems to have some database
comparison tools that allow you to compare the differences between the
database and the checked in scripts. That's only useful if people are
updating the database directly and not checking out the scripts,
though. I suppose it's also helpful for installing on other systems
that have older versions of the database though too.

I am not aware of any source control features for SQL Server in Team
Foundation. Nothing I've read on TFS has indicated an integrated
solution for SQL object source control.

If you use the SSMS graphic UI to update your SQL objects, you'll find
that the file comparison isn't as helpful as if you manage the objects
in script and keep those scripts nicely formatted. I think this is
one key reason why MS hasn't implemented any such functionality. If
you do that anyway, you don't need a special tool.

-Eric Isaacs

 | 
Pages: 1
Prev: batch file
Next: Organizing Solutions and Projects