| Author |
Topic |
|
chrispy
Posting Yak Master
107 Posts |
 Posted - 2002-02-23 : 22:24:02
|
| All, I have a number of SPs in SQL 2000 that I need to backup. What is the best procedure/ technique for backing up SPs? Other than spending numerous hours saving to text files.Thanks in advance,Chris |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-02-23 : 22:38:42
|
You can create the text files in about 3 minutes. From Enterprise Manager go to "Generate Scripts", choose all the procs and set the option to generate a file per object.<lecture>While you are at it, save all your scripts in source control, and only make code changes from your scripts. Having developers making changes in Enterprise Manager on a live system is ASKING for trouble. Trust me, put a good process in place before it really bites you. This is a lesson you don't want to learn the hard way  </lecture>Damian |
 |
|
|
Spyder
SQLTeam Author
75 Posts |
Posted - 2002-02-24 : 05:20:59
|
Ditto on what Merkin says. I also find it quite helpful to include some header text for stored procs, triggers, and functions that describe the object type, name, version, who created it, who modified it last, and when. Here's an example:/****** Stored Procedure dbo.sp_MyProc v 1.21 ******//****** Created 11/15/2000 by Joe Blow ******//****** Modified 12/18/2000 by John Doe ******/CREATE PROCEDURE .... Edited by - SPYDER on 02/24/2002 05:22:02 |
|
|
|
nr
SQLTeam MVY
12543 Posts |
|
|
chrispy
Posting Yak Master
107 Posts |
Posted - 2002-02-24 : 09:39:32
|
| All, Again I thought that I had replied to this.???I do have them backed up. Thanks for the help. Curious as to why there is no way to backup SPs during the normal MS SQL backup. Or am I missing it?Thanks again,Chris |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-02-24 : 10:23:14
|
| SPs are objects in the database so they get backed up with the database backup.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
chrispy
Posting Yak Master
107 Posts |
Posted - 2002-02-24 : 10:44:33
|
| nr, Where can i find more info on this. Can I assume that when I restore a db the SPs are restored as well? Is there a way to selectively restore SPs ?Educate me.Chris |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-02-24 : 12:05:16
|
| The database will contain the tables (and data therein), indexes, triggers, stored procedures, users and permissions and other things I have missed probably.When you restore a database everything is restored to make it a usable entity.Basically you are replacing the database from the time of the backup.Stored procedures and triggers are not user changable entities and hence the backup/ database should not be considered the source to which you go to replace them. In fact the only thing that the backup is necessary for is the data - everything else is under control of the dba.See my article on relase and development procedures - this gives a way of developing and releasing versions keeping a history of all releases and the scripts of everything on the database.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-02-24 : 16:48:41
|
| Chrispy,One of the fundamental attributes of a database system, is that it must keep all information relating to the database in the database. It may seem obvious, but several databases out there do not do this...SQl Server does a great job at this...DavidMTomorrow is the same day as Today was the day before. |
|
|
|
chrispy
Posting Yak Master
107 Posts |
Posted - 2002-02-24 : 17:00:27
|
| Thanks all, Now for sure I have a clear understanding rather than an assumption/guess. As I move closer on this project (web site / sales site for higher end networking equipment, my expertise if I can ever return a favor), I spent this afternoon restoring a db and sure enough everything was there. That makes a lot of sense, but I have learned that nothing is for granted. Safe to assume that backuping up SPs seperate from the normal backup is for the speed and convience. Thaks again,Chris |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-02-25 : 01:11:58
|
| >> SQl Server does a great job at this...It does now - didn't in the past.Was always ok with SPs though.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-02-25 : 01:16:48
|
quote:
>> SQl Server does a great job at this...It does now - didn't in the past.Was always ok with SPs though.
Thanks NR... Just as I had recovered from that recurring nightnmare that was 6.5..Shall I send the therapist bill to your address?DavidMTomorrow is the same day as Today was the day before. |
|
|
|
|
Backuping up Stored Procudes