CODEWITHSUNDEEP
sql-serverdatabaset-sql
MAS
MAS

Reputation: 311

Msg 5011, Level 14, State 5, Line 11 ALTER DATABASE statement failed

I ran the below scripts

DECLARE @name VARCHAR(50)

DECLARE @Statment VARCHAR(500)

DECLARE DB_cursor CURSOR
FOR
SELECT  name
FROM    sys.databases
WHERE   name NOT IN ( 'master', 'tempdb', 'model', 'msdb',
                      'ReportServer$SQLEXPRESS',
                      'ReportServer$SQLEXPRESSTempDB' )

OPEN DB_cursor

FETCH NEXT FROM DB_cursor INTO @name

WHILE @@FETCH_STATUS = 0 
BEGIN

    PRINT @name

    ALTER DATABASE [@name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    SET @Statment = 'Backup database ' + @name + ' To disk =N' + ''''
        + 'C:\Mas\' + @name + '.bak' + ''''

    PRINT @Statment

    EXEC(@Statment)
-- ALTER DATABASE [@name] SET MULTI_USER WITH ROLLBACK IMMEDIATE
-- EXEC sp_dboption @name, N'offline', N'false'
    FETCH NEXT FROM DB_cursor INTO @name
END 
CLOSE DB_cursor   
DEALLOCATE DB_cursor

I got the error below

Msg 5011, Level 14, State 5, Line 11
User does not have permission to alter database '@name', the database does not exist, or the database is not in a state that allows access checks.

Msg 5069, Level 16, State 1, Line 11
ALTER DATABASE statement failed.

Please let me know where I am stuck on how to resolve this issue.

Note:- I ran this script in sa login.

Upvotes: 0

Views: 20077

Answers (4)

Deepshikha
Deepshikha

Reputation: 10264

Error message clearly states the reason. It's because Alter database command treats [@name] as a database and not a placeholder. You can include both the statements in dynamic query and then execute. Following should work:

DECLARE @name VARCHAR(50)
DECLARE @Statment VARCHAR(500)
DECLARE @Statment1 VARCHAR(500)
DECLARE @Statment2 VARCHAR(500)

select name from sys.databases where name NOT IN('master','tempdb','model','msdb','ReportServer$SQLEXPRESS','ReportServer$SQLEXPRESSTempDB')

OPEN DB_cursor

FETCH NEXT FROM DB_cursor INTO @name

WHILE @@FETCH_STATUS = 0 

BEGIN

Print @name

SET @Statment = 'ALTER DATABASE ' +  @name + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE; '
EXEC(@Statment)
   SET @Statment1 = 'BACKUP DATABASE '  + @name + ' To disk =N' +''''+ 'C:\Mas\'+ @name          +'.bak'+''''
EXEC(@Statment1)
SET @Statment2 = 'ALTER DATABASE ' +  @name + ' SET MULTI_USER WITH ROLLBACK IMMEDIATE; '
EXEC(@Statment2)

EXEC(@Statment)
-- ALTER DATABASE [@name] SET MULTI_USER WITH ROLLBACK IMMEDIATE
-- EXEC sp_dboption @name, N'offline', N'false'
FETCH NEXT FROM DB_cursor INTO @name
END 
CLOSE DB_cursor   
DEALLOCATE DB_cursor

Upvotes: 3

Ardalan Shahgholi
Ardalan Shahgholi

Reputation: 12565

I change your T-SQL and this worked.

you most change destination of back up file in the anther path like this sample .

DECLARE @name VARCHAR(50)

DECLARE @Statment VARCHAR(500)

DECLARE DB_cursor CURSOR
FOR
SELECT  name
FROM    sys.databases
WHERE   name NOT IN ( 'master', 'tempdb', 'model', 'msdb',
                      'ReportServer$SQLEXPRESS',
                      'ReportServer$SQLEXPRESSTempDB' )

OPEN DB_cursor

FETCH NEXT FROM DB_cursor INTO @name

WHILE @@FETCH_STATUS = 0 
    BEGIN

    PRINT @name
--      DECLARE @A NVARCHAR(800) = ' ALTER DATABASE [' + @name + '] SET Multi_USER  WITH ROLLBACK IMMEDIATE '
    DECLARE @A NVARCHAR(800) = ' ALTER DATABASE [' + @name + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE '
    EXECUTE Sp_ExecuteSQL @A

    SET @Statment = 'Backup database ' + @name + ' To disk =N' + ''''
        + 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER2012\MSSQL\Backup\' 
        + @name + '.bak' + ''''

    PRINT @Statment

    EXEC(@Statment)
-- ALTER DATABASE [@name] SET MULTI_USER WITH ROLLBACK IMMEDIATE
-- EXEC sp_dboption @name, N'offline', N'false'
    FETCH NEXT FROM DB_cursor INTO @name
END 
CLOSE DB_cursor   
DEALLOCATE DB_cursor

Like down picture

you most grate access to SQL Server user in your path of Back up

enter image description here

Upvotes: 1

Ardalan Shahgholi
Ardalan Shahgholi

Reputation: 12565

You most use SP_ExecuteSQL for change state of Database

DECLARE @A NVARCHAR(800) = ' ALTER DATABASE [' + 
                           @name + 
                           '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE '
EXECUTE Sp_ExecuteSQL @A

And then you can set database in single user mode.

Upvotes: 0

Dijkgraaf
Dijkgraaf

Reputation: 11527

The SQL command ALTER DATABASE does not allow a parameter hence it is treating @name as being the actual name and not as a parameter.

ALTER DATABASE

NAME Specifies a logical name for the file. logical_file_name Is the name used in Microsoft SQL Server when referencing the file. The name must be unique within the database and conform to the rules for identifiers. The name can be a character or Unicode constant, a regular identifier, or a delimited identifier. For more information, see Using Identifiers.

Upvotes: 0

Related Questions