June 14, 2005 at 10:24 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/saveyourstoredprocedures.asp
June 17, 2005 at 5:29 am
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.
June 17, 2005 at 5:53 am
You could add a date/time stamp to the file name, to allow developers to go "back in time"
Great article
June 17, 2005 at 6:35 am
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.
June 17, 2005 at 7:18 am
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.
June 17, 2005 at 8:44 am
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.
June 17, 2005 at 9:31 am
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 (http://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
June 17, 2005 at 11:43 am
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
June 17, 2005 at 4:03 pm
What is the best way to execute this script?
June 17, 2005 at 4:08 pm
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
June 20, 2005 at 4:06 am
Just a warning note about the EXCLUDE list - you should probably add start and end delimiters to your excluded DB names to prevent a custom DB called My_master being excluded too.
So the excluded list would be /master/model/...etc.../ and the comparison code would be with "/" & DBName & "/" rather than just DBName on its own.
June 22, 2005 at 4:08 am
Hi,
Great article & good tips along the way in the reponses. Keeping your stored procedures in a source controlled environment is as the article states rather tricky. Using something like this is a great way to achieve it. Also posted in the responses was asked why not keep the drop script included in the script.
In the code posted you'll see the commented out part being ''262150= SQLDMOScript_ObjectPermissions + SQLDMOScript_PrimaryObject + SQLDMOScript_OwnerQualify
Then in the next line of the code, the 262150 is used in the script method. You can find the values of these Script Method Constants in MSDN. In the posted code 262150 = 2 + 4 + 262144. You can add for instance SQLDMOScript_Drops (being the constant value of 1) to the equation - thus changing the value of 262150 to 262151.
Amend the code to the value 262151 like this: oProc.Script 262151, dbFolder & "\" & replace(oProc.Owner, "\", "-") & "_" & replace(oProc.Name, "\", "-") & ".sql"
Run it again, and check the script generated will contain the drop script as well.
June 20, 2006 at 6:30 am
As a newbie I would love to implement this and can follow the logic in the command but have no idea how to run it. Apparently you all have a knowledge of VB script but for me, with no such knowledge, it would be good to have a 1,2,3 on how to run this easily. Is there a source for downloadng the tools I need to run this?
We recently had a SP get trashed so this type of backup would be excellent to have.
June 20, 2006 at 7:26 am
You do not need any tools to run this. Just copy the code that is posted in the article, open notepad and paste the code in a new text document. Save the file with a ".vbs" extension and double click on the file to run it. Depending on the Windows Version you are running, you may have a dialog box prompt you that running scripts may not be safe do you wish to run the script or block it. From the dialog box select to trust or allow the script to run.
Requirements
Hope this helps.
November 11, 2007 at 10:14 pm
What if the stored procedures are encypted? I think if the utility can allow backup and restore only the partial components of DB such as stored procedures, triggers, view, tables, then it will be great. Anyone have any idea?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply