Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Save Your Stored Procedures Expand / Collapse
Author
Message
Posted Tuesday, June 14, 2005 10:24 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Thursday, December 18, 2014 6:56 AM
Points: 6,804, Visits: 1,934
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/saveyourstoredprocedures.asp

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #190467
Posted Friday, June 17, 2005 5:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 10, 2014 12:59 PM
Points: 8, Visits: 200
Andy,

Why not treat database code in the same manner that application source code is treated and work from a real version control system? We are using Visual Studio 2003 and although I'm not particularly fond of the editor, it allows us to set up database projects and have all source code in VSS. I had one of our developers create a Visual Studio add-in that creates a database build script from the source files in a project. It is working out very well thus far.



Post #191612
Posted Friday, June 17, 2005 5:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:00 AM
Points: 1, Visits: 42

You could add a date/time stamp to the file name, to allow developers to go "back in time"

Great article




Post #191614
Posted Friday, June 17, 2005 6:35 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 7, 2013 11:44 AM
Points: 51, Visits: 40
I agree, I've always put each SProc in a file and checked it in and out of VSS. Great way to maintain history. Query Ananlyzer's ability to edit SProcs directly, instead of loading a file and running it in, just gets folks in trouble.


Post #191627
Posted Friday, June 17, 2005 7:18 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 27, 2014 7:06 AM
Points: 7, Visits: 7
This is great. I just finished making some table and proc changes, and have been putting off making the changes to source safe, since I knew that the source safe files were not up to date and I really needed to do all the objects again. I took your code and created a VB app, added a combo box to select which objects to script and bam project complete.
Post #191641
Posted Friday, June 17, 2005 8:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 9, 2013 8:19 PM
Points: 12, Visits: 23

Great Article, definitely a keeper.

I would like to reply to ErikEJ's comment about adding a date/time stamp to the file name.  This is not a good idea as was mentioned in the article.  The purpose of this script is to produce files which can be used with a version control tool such as StarTeam or Rational ClearCase.  By adding the date to the file name or in the content of the file would defeat this purpose.  If you are not using version control software and wish to keep separate snapshots of the database then the best thing would be to create a folder by date of better yet zip the files and add the date to the name of the zip file.

I plan to modify this script to script other objects as well.




Post #191687
Posted Friday, June 17, 2005 9:31 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 1:21 AM
Points: 73, Visits: 69

Storing the drop/create scripts for stored procedures and, indeed, every other type of database object in a source control/configuration management system should be standard practice for any development shop.  You do it without question for application code, why not for database code?

And there's the rub...

Database code isn't like normal application code.  You can't just build a brand new database and replace production with it.

But what if you had a magic black box that could analyse the individual drop/create scripts under source control for changes and apply those intelligently to a production database with no loss of data?

This was the question we aimed to solve three years ago and the 'black box solution' is now a product called DB Ghost (www.dbghost.com).

To release your latest set of code simply label it all in your SCS and extract it to disk.  DB Ghost will then:

1. Build a brand new database from the source scripts.  This ensures that a) no syntax errors exist and b) no dependency errors have been introduced.  This is the equivalent of a daily build but for databases.

2. Compare a target database to the newly built source database and modify the target to match it EXACTLY, no more, no less.

What you are effectively left with is a target database that matches a known, labelled set of source.

The benefits of this approach are many but the main one is that developers work with check out/check in for ALL databases objects just as they do for other application code.  This means that our approach is perfectly aligned with configuration management best principles as it deals with source code.

There is no other tool available today that does this.

The only downside? We need to feed our families so we charge for it.

How much is your time worth and how much of it do you spend on troubleshooting deployment and script collisions? 

DB Ghost could make those issues go away forever.



Malcolm
DB Ghost - Build, compare and synchronize from source control = Database Change Management for SQL Server
www.dbghost.com
Post #191737
Posted Friday, June 17, 2005 11:43 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, June 21, 2011 10:03 AM
Points: 577, Visits: 102
Nice, Steve! That's very helpful, actually; and it keeps all the formatting, too. Great way to rip a database. Once it's in SCC you can deploy like normal, but this is great to get started, or to make sure you SCC repository is up to date.

Seems like with a little SQLDMO you could create simple scripts for backing up your functions and views as well. Probably backing up tables AND their indexes/constraints would be the hardest. I'll have to look into this more...

PS: It's great to see that you can offer free code like this and still feed your family.


Signature is NULL
Post #191796
Posted Friday, June 17, 2005 4:03 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 17, 2006 8:21 PM
Points: 21, Visits: 1
What is the best way to execute this script?
Post #191866
Posted Friday, June 17, 2005 4:08 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, June 21, 2011 10:03 AM
Points: 577, Visits: 102
Anything that runs VB; or you can compile it to run on it's own.

Personally, I used DTS (ActiveX step); that was the fastest.

cl


Signature is NULL
Post #191867
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse