Stored procedure to alter database recovery mode isnt compiling

Question

I am trying to create a stored procedure that would be generic. I am trying to alter a database and set the recovery mode to either simple or full. It would accept database name and mode as parameter.

The SQL query executes in the context of the master database and alters the database specified. I am trying to incorporate it via Execute SQL task in SSIS. I need the stored procedure to reside in the database that is going to perform the operation on. Not sure how that is going to work. USE database keyword is not allowed in the stored procedure...

The original query works fine but I am facing an issue while trying to execute the stored procedure in the database.It says 'RECOVERY' is not a recognized SET option.

Original query:

use master 

ALTER DATABASE XYZ
SET RECOVERY FULL

Stored procedure:

USE XYZ
GO

CREATE PROCEDURE DatabaseRecoveryMode
    (@mode varchar(10),
     @database varchar(50))
AS
BEGIN
    ALTER DATABASE @database
    SET RECOVERY @mode
END

Possible duplicate of [How to use a variable for the database name in T-SQL?](https://stackoverflow.com/questions/727788/how-to-use-a-variable-for-the-database-name-in-t-sql) — Tab Alleman, Oct 24 '17 at 13:24

score 1 · Answer 1 · answered Oct 24 '17 at 11:57

The ALTER DATABASE documentation shows the recovery model is a keyword, not a variable. You'll need to construct and execute a dynamic SQL statement for this.

CREATE PROCEDURE dbo.DatabaseRecoveryMode
(
    @mode nvarchar(11),
    @database sysname
)
AS
IF @mode NOT IN(N'SIMPLE', N'BULK_LOGGED', N'FULL')
BEGIN
    RAISERROR('Recovery model must be SIMPLE, BULK_LOGGED, OR FULL', 16, 1);
    RETURN 1;
END;

DECLARE @SQL nvarchar(MAX) = N'ALTER DATABASE ' 
    + QUOTENAME(@database) 
    + N' SET RECOVERY '+ @mode + N';';

EXECUTE(@SQL);
GO

Mazhar · Answer 2 · 2017-10-24T12:02:04.113

0

You need to use dynamic SQL

USE XYZ
GO
Create Procedure DatabaseRecoveryMode
(
    @mode varchar(10),
    @database varchar(50)
)
AS
begin

DECLARE @SQL NVARCHAR(MAX)
DECLARE @db NVARCHAR(60), @Use NVARCHAR(100)
SET @db = N'master' 
SET @Use = N'Use ' + @db

SET @SQL = @Use + N' ALTER DATABASE '+ @database + N' SET RECOVERY ' + @mode ;
--SELECT @SQL

EXEC sys.sp_executesql @SQL ;

end
GO

edited Oct 24 '17 at 12:02

answered Oct 24 '17 at 11:54

Mazhar

3,797
1
12
29

Stored procedure to alter database recovery mode isnt compiling

2 Answers2