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 Monday, June 20, 2005 4:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 20, 2011 4:37 AM
Points: 114, Visits: 78

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.

Post #191987
Posted Wednesday, June 22, 2005 4:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 19, 2013 2:34 AM
Points: 11, Visits: 42

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.




Post #192808
Posted Tuesday, June 20, 2006 6:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 20, 2006 9:49 AM
Points: 1, Visits: 1
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.
Post #288744
Posted Tuesday, June 20, 2006 7:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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

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

  1. Windows OS (Windows 98 may be OK, but I know Windows 2000 + will work.)
  2. Windows Scripting Host (You may already have it installed.  If not do a search on Microsoft's Web Site.)

Hope this helps.




Post #288771
Posted Sunday, November 11, 2007 10:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 21, 2008 9:15 PM
Points: 1, Visits: 1
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?
Post #420912
Posted Wednesday, April 30, 2008 6:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 28, 2009 7:20 AM
Points: 2, Visits: 10
BRILLIANT! Thanks!
Post #492899
Posted Friday, April 24, 2009 11:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 14, 2014 6:19 AM
Points: 7, Visits: 79
Thank you! I have have been looking for something to dump out the SPROCS for months and this works perfectly.
Post #704182
Posted Monday, May 4, 2009 12:57 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 14, 2014 1:19 PM
Points: 323, Visits: 1,456
Wow, found this 4 years later! A helpful way to quickly get our procs and tables saved into text files off to a backup drive.

Thanks,
Ken
Post #709644
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse