Help with Stored Procedure

[dcf1023]

I'm having trouble getting a command to run in a stored procedure to bring some databases online that had previously been restored with no recovery. In the stored procedure the code is:

CREATE Procedure sp_RecoverAllDatabases
BEGIN...

RESTORE DATABASE productinfo WITH RECOVERY
...

I get an error the following error:
Server: Msg 927, Level 14, State 2, Procedure sp_RecoverAllDatabases, Line 15
Database 'productinfo' cannot be opened. It is in the middle of a restore.

However, if I run the line "RESTORE DATABASE productinfo WITH RECOVERY" in query analyzer, it restores the database and brings it back on line. Any idea what the core issue is here?

Thanks in advance for any help.

 

[SQLBill]

You are either:

Trying to create the procedure in the database that is recovering. Which you can't do until it recovers.

Trying to exec a procedure that is in the database that is recovering. Can't do this either.

QA works because you are not using something IN the database. You are running a script AGAINST a database.

FYI-it's poor coding to create a procedure that has a name beginning with SP_ (or sp_). Why? Because when it's run, SQL Server will first try to find it in the Master database since that (sp_) is the built-in identifier for SQL Server stored procedures. It's suggested that you use something else such as usp_ (user stored procedure) or maybe even the initials of the programmer (bfsp_).

-SQLBill

 

[dcf1023]

SQLBill,

This stored procedure lives in the master database and is running inside the database that is trying to be recovered. Any other ideas?

 

[rgb30b]

I dont have an answer but I do have a comment about this..

----FYI-it's poor coding to create a procedure that has a name beginning with SP_ (or sp_). Why? Because when it's run, SQL Server will first try to find it in the Master database since that (sp_) is the built-in identifier for SQL Server stored procedures. It's suggested that you use something else such as usp_ (user stored procedure) or maybe even the initials of the programmer (bfsp_).-----

My thoughts EXACTLY....

 

[dcf1023]

Thank you for weighing in, even though you had nothing useful to say.

 

[SQLBill]

I haven't been able to find anything more on this. I'll keep looking.

-SQLBill

 

[SQLBill]

Soon as I posted the above, I got hit by an idea.

When a database is being restored, it's in SINGLE-USER mode. Since YOU are restoring the database, YOU can run commands against the database. When you used Query Analyzer, you were running the command as you (the same user that is restoring the database). I'll bet that when a stored procedure is run, it's being run under a different 'user' or system process (SPID).

Here's a test to run....open up query analyzer using a different user login (try SA). Run the command and see if it restores the database.

-SQLBill