September 22, 2012 at 4:32 am
Hello,
I have created a SP(Even though im not good at it :)..). The SP is used for simple backup & restore. i thought of restricting the DB before restoring & putting the Db to Multi user after restoring. I need help to use below alter commands in SP, with out causing any issues.
You can see SP below:[/u]
= @DataBase
) = 1
BEGIN
RESTORE DATABASE @DataBase FROM DISK = @FilePath
END
ELSE
PRINT 'Database does not exist in the Server.'
END
END
GO"]
Thanks
September 22, 2012 at 5:42 am
You need to use dynamic SQL for this as ALTER DATABASE cannot use variables for the database name as it is not a literal string.
declare @statement nvarchar(max)
set @statement=N'ALTER DATABASE '+QUOTENAME(@databasename)+' SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE'
exec sp_executesql @statement
September 22, 2012 at 6:06 am
Hi,
Thanks for the reply, I have got it now.
Thanks again 😀
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply