Sasa Sasa - 5 months ago 19
SQL Question

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?

Answer

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.

Comments