Backup Stored Procs? - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Backup Stored Procs?

Author

Topic

MrRay
Starting Member

28 Posts

Posted - 2005-06-21 : 09:12:09

Hey,

I'm running SQL2000 and I need to know how to backup and import/export a database INCLUDING the stored procedures.

Every time I do test backup/restore, the stored procedures aren't backed up. And the same goes for copying the database from one server to another - stored procedures aren't included.

I need a reliable way to backup my complete database exactly as it is today. Can someone help?

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-06-21 : 09:19:11

That is really weird. I assume you are using the BACKUP and RESTORE commands or the equivalent stuff in Enterprise Mangler and that you are creating the procedures in the databases you are backing up.

Sean Roussy

Please backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-21 : 09:21:27

Backup database DBname to disk='C:\test.bak'
and restore it when neccessary

Madhivanan

Failing to plan is Planning to fail

MrRay
Starting Member

28 Posts

Posted - 2005-06-21 : 09:31:14

Yep, I created a test database with some tables and a single stored procedure. I hit "Backup Database" and saved the .bak file to the local disk.

I emptied the test table and deleted the stored procedure, then hit "Restore Database", and it didn't restore the stored procedure.

And the Import/Export option only copies tables & views.

Any ideas?

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-06-21 : 10:01:17

What I want you to do is to not use the mangler. In the QA I want you to confirm that you are in the database that you are backing up before you recreate your stored proc.

Next I want you to use the BACKUP command that madhivanan demonstrated. use the DROP PROC command to drop your proc and then use the RESTORE command.

Sean Roussy

Please backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.

MrRay
Starting Member

28 Posts

Posted - 2005-06-21 : 10:28:35

It looks like Enterprise Mangler (which is what I will now be calling it) isn't as reliable as I had thought.

Doing it through QA made a difference - the stored procs I deleted were nicely restored when I restored from backup.

Can I continue this with another question? It looks like an instance of SQL Server cannot restore from a backup that it didn't do. (Correct me if I'm wrong there.)

I would like to be able to make a backup of the data on our main SQL server and use it to restore a database when 1) our main server fails, or 2) we decide to move it over to another database. (Exporting data doesn't include stored procs, so I think a backup file is my best option.)

Any suggestions on that front?

Thanks to you both.

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-06-21 : 14:45:45

you want to look at the WITH MOVE option for the restore command. And yes scripting things out is almost always better than the mangler.

Sean Roussy

Please backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.

drtherc
Starting Member

19 Posts

Posted - 2005-06-22 : 16:34:34

I'd also suggest scripting the objects (views, sp's, rules, functions, table structures) so that you can R&D wherever necessary (that's rip off and duplicate).

David

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-22 : 19:02:22

It sounds like you just needed to refresh the screen after the restore as EM cached the data so you were seeing the before image and not the after image.

Tara

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-06-22 : 20:55:07

That is wrong; I do it all the time.

SQL Server 2000 can restore databases backups created with SQL Server 7.0 or SQL Server 2000.

quote:
Originally posted by MrRay
...Can I continue this with another question? It looks like an instance of SQL Server cannot restore from a backup that it didn't do. (Correct me if I'm wrong there.)...

CODO ERGO SUM

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-06-22 : 21:16:07

quote:
Originally posted by tduggan

It sounds like you just needed to refresh the screen after the restore as EM cached the data so you were seeing the before image and not the after image.

Tara

I'm with Tara on this, nearly everything in EM has to be refreshed to see the "result".

EM = Right Click & Refresh

Beauty is in the eyes of the beerholder

MrRay
Starting Member

28 Posts

Posted - 2005-06-22 : 22:19:35

How does one go about this? When I hit "Restore Backup", it simply shows an empty list of previous backups, and I don't see an option to manually select the backup file to restore.

My goal is to save a backup and restore it on another SQL server, allowing me to quickly restore the database on another PC should the primary go down.

quote:
Originally posted by Michael Valentine Jones

That is wrong; I do it all the time.

SQL Server 2000 can restore databases backups created with SQL Server 7.0 or SQL Server 2000.

CODO ERGO SUM

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-06-23 : 04:57:10

In EM change the radio button to "From device" then add/select a device by browsing to the backup file

Andy

Beauty is in the eyes of the beerholder

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-06-23 : 08:50:18

siiiiigggghhhhhh. Or you can be a big boy and learn how to script everything wich will pay off in the long run.

Sean Roussy

Please backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.

I am available for consulting work. Just email me though the forum.

MrRay
Starting Member

28 Posts

Posted - 2005-06-23 : 11:06:38

Well, Sean Roussy, learning to script everything doesn't help me protect my data today, does it? :) I'm not going to tell my superiors "Ok, so once I take the time to learn to script everything, our data will be backed up & restorable." I need to ensure that we're protected today.

At your recommendation, I will learn to script everything. Until then, at least I can now say with confidence that I have a backup/restore solution.

Thanks to you all.

quote:
Originally posted by Thrasymachus

siiiiigggghhhhhh. Or you can be a big boy and learn how to script everything wich will pay off in the long run.

Sean Roussy

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-06-23 : 15:20:35

It is good advice to use scripts instead of EM.
Now that you have solved your immediate problem why dont you try and do the same through script - obviously in a test environment

Andy

Beauty is in the eyes of the beerholder

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2005-06-24 : 10:35:29

Something that may help: You can use SQL Profiler to capture what T-SQL commands Enterprise Mangler is generating. Use those commands as a template, and you are halfway home. Books OnLine (BOL) will get you the rest of the way there.

Crito
Starting Member

40 Posts

Posted - 2005-06-24 : 11:10:37

Refresh doesn't always do it. You need to disconnect then reconnect to the db in EM to be sure. Happens all the time, especially when two people use same proc name but under their own schema (rather than dbo's.)

Still good advice to use scripts tho...

ssrikrish
Yak Posting Veteran

69 Posts

Posted - 2005-08-04 : 22:45:11

I would recommend scripting rather than using EM for backup/restore operations. I have had instances wherein when I try to restore a database using EM, it actually dropped the database. Thank god it was only in TEST environment :-))

--sri

Sitka
Aged Yak Warrior

571 Posts

Posted - 2005-08-05 : 09:23:13

Refresh / Ent. Mangler.
When I was first trying to figure out orphaned users, did I ever cuss at that thing.
Now I just assume it's always wrong and am much happier.
Low expectations mean it never dissapoints, and a wonderful calm envelops the halls of IT.

I wish someone would start an Official XML Rant Thread.

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2005-08-05 : 10:31:04

In 5 years, I have yet to have a problem with EM for doing quick backups and restores for testing or development purposes. But I am always open to learning new stuff. Any good resources on what the scripts look like to do this? (first person to say "Check BOL." gets a swift kick!)

Aj

Hey, it compiles.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources