Restores thru stored procs not working in query analzyer

Question

Restores thru stored procs not working in query analzyer

Juanita

SSCertifiable

Points: 5300
More actions
April 2, 2004 at 9:21 am

#107600

HI. Is there any reason why if I execute a stored procedure in query analyzer to do a very basic backup of database, not work?? I can take the actual RESTORE statement and run that in query analyzer and it works fine, but once i try to run the store proc with the same stmt in it, i get:
Server: Msg 3101, Level 16, State 2, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I'm running the stored proc in the context of the MASTER database. Now i can execute a similiar stored proc that does the database BACKUP and it runs fine all the time.
any suggestions? i'm sure it's something stupid i'm doing.
Juanita

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 1

Are you using the database when you attempt the restore? Confirm that you are not in the db when trying, also run and sp_who to check that there are no users in the db.

Shamless self promotion - read my blog http://sirsql.net

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 2

The above suggestion is correct. ANY user, including yourself that has a session open to that db has a shared lock on the db.

Juanita SSCertifiable Points: 5300 More actions · Answer 3

HI. I have done the SP_WHO and there is no user, not even me, that has a session open with this database. i'm in query analyzer and i have NOTHING open with this database, not even in enterprise manager. i even did the ALTER DATABASE webfaq SET SINGLE_USER command to see if that would help. and i still get the message that the database is in use. maybe i will stop/restart the server to see if there is possibly somehting hung up outthere that is not showing in query anlyzer.

Keep the ideas coming.

Juanita

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 4

Can you post the code of the proc and how you call it?

Shamless self promotion - read my blog http://sirsql.net

Juanita SSCertifiable Points: 5300 More actions · Answer 5

Okay, in query analyzer here is the statement i'm executing in the context of MASTER:

EXEC [Webfaq].[dbo].[sprocWebfaq_Restore]

Now the actual stored proc looks like this:

CREATE PROCEDURE sprocWebfaq_Restore AS

-- Restore the full webfaq database.

RESTORE DATABASE webfaq FROM webfaq_db_backup with RECOVERY

GO

I'm using a disk backup device whick does contain a backup.

Juanita

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 6

The problem is that you are calling the proc which exists in the database, therefore you have a connection to the database and so cannot restore it.

Try creating an Admin database to store your administration scripts and then execute it from there. The restore should work.

Shamless self promotion - read my blog http://sirsql.net

Bivs SSC Enthusiast Points: 159 More actions · Answer 7

Just wondering... Why are you doing this through a stored proc.

Juanita SSCertifiable Points: 5300 More actions · Answer 8

THANK YOU THANK YOU!!!! That was it!! I just copied the stored proc to another database and it ran fine. It just wasn't registering what I was doing and when I didn't the see connection in sp_who, I was thrown for a loop.

Thank you so much for your help!

Juanita