Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rudba
Constraint Violating Yak Guru
415 Posts |
 Posted - 2010-02-05 : 16:17:48
|
| How to copy all the stored procedures from one db to anothere db on save sql server. |
|
|
rudba
Constraint Violating Yak Guru
415 Posts |
Posted - 2010-02-05 : 16:24:02
|
| I got it.Right Click your database -> Tasks->Generate Scripts |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-06 : 02:00:02
|
| We save all our Sprocs to disk (and use a Version Control system so we can see what changed over time!!)Then we can just run all the "new" ones against the various databases that need them."new" is any stored procedure with a modified date newer than the last time we updated that database.(We actually take it a little further and have some logging code at the top of each Sproc source code file that logs that it was run, and what its version is, so that we can easily see what has been run and what hasn't. Ask if you need more details) |
|
|
|
haroon2k9
Constraint Violating Yak Guru
328 Posts |
Posted - 2010-02-06 : 03:15:10
|
| Hi,how we handling is,when first patch(sql scripts)deployed in productionserver,we label it in vss(Version control system)from then on if we create any new objetcs like(tbl,sproc,udf..etc),we do note down the name of the objects what we created in txt format on a disk..everyone of our team members do it this way,atlast when we do deploy next patch,we all sit together and collect the objects all at once wjhat they have done so far..then we execute the script.we had faced many problem by doing so..some of them missed out some procedure and other stuffs we found after production..please advice me how to handle this kind of stuffs... we know we are doing mad thing..kristen,good day.i dint understand this.could please help me out "new" is any stored procedure with a modified date newer than the last time we updated that database.(We actually take it a little further and have some logging code at the top of each Sproc source code file that logs that it was run, and what its version is, so that we can easily see what has been run and what hasn't. Ask if you need more details) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-06 : 04:21:05
|
Sounds like you are relying on people making a note of what they did. Someone will forget, someday!We never change the database from the Table Design tool. We always made a script, and save the script to disk (we use a filename that is in sequence).So all the .SQL files (table changes and other DDL scripts, Sprocs, Triggers, etc) are in one folder, and we can select all the scripts that are new since the last time we rolled out to the database.We do this on our DEV database, then we repeat on our QA database, and perform all the tests.Then for each client we perform the rollout on a TEST copy of the database, the client approves, and then we rollout on LIVE/PRODUCTION database.We make the exact same steps in every case, so there is no variation, and therefore, hopefully!, no mistakes.At the top of each script I have:EXEC dbo.USP_LogScriptRun 'MySproc', '100206'GOCREATE PROCEDURE dbo.MySproc... The Sproc "LogScriptRun" records the script name parameter ("MySProc"), the version ("100206"), today's date, which server it was run on (in case we backup the DB and restore on another server), and so on.We have a script to compare the Script Names, versions and run dates on two databases - so we can compare TEST and LIVE and check that everything that has been added to TEST, and approved by the client, has then be also added on LIVE. This is particularly helpful if we perform some emergency work on TEST and need to repeat those steps on LIVE later.Also, we can compare our "MODEL" database against each client's database to check that it has all the correct scripts / Sprocs etc. for the "current" version.We have written USP_LogScriptRun so that if we run it with Script Name parameter, but no version parameter, it gives a list of the versions and when installed (including the create date in sysobjects):EXEC dbo.USP_LogScriptRun 'MySproc'From sysobjectsid xtype crdate refdate name----------- ----- ----------------------- ----------------------- -------236540622 P 2010-01-06 11:29:02.563 2010-01-06 11:29:02.563 MySProcFrom my Scripts LogCreate Date Version Server Create User Name----------------------- ------------ ------- ------------ ---------2010-01-06 11:29:01.500 100106 MyServer dbo MySProc2010-01-06 07:48:31.720 100105 MyServer dbo MySProc2010-01-03 11:29:38.283 100102 MyServer dbo MySProc |
|
|
|
haroon2k9
Constraint Violating Yak Guru
328 Posts |
Posted - 2010-02-06 : 04:39:21
|
| thanks kristen.iam trying to understand your logic.iam a beginner.so please tell me what is this sproc EXEC dbo.USP_LogScriptRun?behind the scene.could please show some sample code..dont mistake me..if iam wrong plese excuse me. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-06 : 07:33:55
|
"Please tell me what is this sproc EXEC dbo.USP_LogScriptRun"It just inserts a row into a table (my "Script Logging Table") with the date, name and version. All pretty simple actually. The important thing is to a) include the SProc call at the top of every script and b) to remember to change the version number when making significant changes to the script  |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2010-02-09 : 16:14:26
|
| Another approach would be to use a third party utility (e.g, RedGate SQL Compare). It will investigate the two databases, determine what objects are different and create/execute a SQL script that puts them in sync.Just a thought...=======================================Few things are harder to put up with than the annoyance of a good example. (Mark Twain) |
|
|
|
haroon2k9
Constraint Violating Yak Guru
328 Posts |
Posted - 2010-02-10 : 01:33:09
|
| Thanks kristen and Bustaz Kool. |
|
|
|
|
|
|
|
|
How to Copy all Stored Procedurs