CODEWITHSUNDEEP
sqlsql-serversql-server-2014
user4226899
user4226899

Reputation:

How can i use master db in stored procedures

I'm trying to create restore db proc. I encountered a problem because i cannot use the command 'use master'. I have try with dynamic SQL but there is no result: My code:

alter proc dbo.RestoreDB
 (
 @location as varchar(4000) 
 )

as
begin

declare @setMasterDb as varchar(400) = 'use master' 

exec (@setMasterDb);


ALTER DATABASE [testDb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [testDb] FROM  DISK = @location WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5
ALTER DATABASE [testDb] SET MULTI_USER

end

GO

Is there workaround?

Upvotes: 1

Views: 2781

Answers (1)

user4650451
user4650451

Reputation:

Switching database contexts (USE someDBName) in the middle of a Stored Proc (or function) is not allowed. Also, you do not need to be pointed at the master db context to run a BACKUP/RESTORE, you can be pointed to almost any DB. To fix your issue, just remove your DB switching and point your query window any DB except the one you want to restore.

EDIT: Updated to point to any DB EXCEPT the one you're trying to restore. Thanks to @DMason for that comment.

Upvotes: 3

Related Questions