December 24, 2002 at 8:36 am
Hi,
I'm trying to build a script that will give me a version control of all stored procedures that will allow me to rollback to a previous version if I need to. I don't want to use VSS, because in my opinion there is too much overhead.
Can anyone point me in the right direction?
Thanks
JMC
JMC
December 24, 2002 at 9:03 am
I guess you could script it out and stuff back into a blob. Why is there too much overhead with VSS? Bill Wunder has a free utility posted here on the site (with source I believe) that does all of the work for you.
Andy
December 24, 2002 at 9:05 am
Possible you could do the following. Not very slick, but might work.
1) Prior to making a SP Change, create a new database call it version_X_Y_Z, where X,Y and Z is the version number.
2) Generate all the SP's from your real database and create the SP in Version_X_Y_Z database.
3) Backup database Version_X_Y_Z to disk.
4) Delete database Version_Z_Y_Z.
Now you have a copy of all your SP's in a dataase backup.
If you want to restore to a particular version do the following:
1) Restore database Version_X_Y_Z
2) Generate SP's from database Version_X_Y_Z
3) Run generated SP script on real database
Bingo your real database now has all teh version X_Y_Z SP on it.
This process could easily be built into a script of some kind. I think, never done it......but it should be possible.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
December 24, 2002 at 10:05 am
Don't try to reinvent this. You will forget something and screw yourself.
Use VSS or CVS or some system designed to do this. Maintian strict controls on this.
http://www.sqlservercentral.com/columnists/sjones/vcspart2.asp
Steve Jones
December 26, 2002 at 6:34 am
Thanks everyone for your input. I just found out that the VStudio CDs are missing, which based on the documentation that I've read I need those CDs.
According to Bill Wunder, the DLL Utility track changes at any given time( which will work in theory). I just need to install test the utility on my own home server, before I start selling my boss on this type of implementation.
I really appreciate everyone's feedback on this. Basically I just need to lay down some foundation work before I can sell the idea of version control using Bill Wunder's utility.
Also was it time consuming to implement this solution, did anything break during installation,etc.
Thanks again
JMC
JMC
December 30, 2002 at 11:04 pm
Very interesting topic!
January 9, 2003 at 12:32 pm
Sorry, it took so long to reply and close this item. Unfortunately I can not access the server and implement anything on the database servers. My work around was this, rebuilt a VB application that tracks the stored procedures in a file format and tracks the versioning via a tree and when clicking the nodes you can view the different versions of the sp and the content. The process that the developers need to do is simple.
1. If new then add to SQL server and then cut and paste and add to the VB app. save as a text file with suffix spName_v1.
2. If sp file already exists, make the new changes on Sql Server, then cut and paste text to VB app, and save as spName_v2;etc for latter versions.
I figure this was a pretty good work around and now we have physical stored procedure files and the interface is very user friendly. Fortunately we have a staff of three for now but any suggestions on how to implement policies as the staff grows will be appreciated.
Thanks
JMC
JMC
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply