What are the challenges you face when working across database platforms? Take the survey
Options

SQL Backup exit code: 890 / SQL error code: 924

sonnydeletejcsonnydeletejc Posts: 5
edited June 13, 2014 4:40AM in SQL Backup Previous Versions
Hi All,

We are having a problem running a restore via a SQL Server Agent job.

This is the message…

Error 890: RESTORE DATABASE permission denied for database: (MyDatabase)
SQL error 924: Database 'MyDatabase' is already open and can only have one user at a time.

SQL Backup exit code: 890
SQL error code: 924


The thing is there doesn’t appear to be any other processes running in the activity monitor.

I haven’t been able to find any posts with a similar combination of exit and error codes.

Can anyone advise?

Many thanks,
J

Comments

  • Options
    peteypetey Posts: 2,358 New member
    Is that particular database set to single-user mode by you? Could you also please post the restore command you are using in the job?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Hi Petey,

    Yes we are setting the database to single user as part of the SQL agent job.

    Here's the full command...
    ALTER DATABASE [MyDatabase] SET MULTI_USER
    GO
    
    ALTER DATABASE [MyDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    GO
    
    WAITFOR DELAY '00:00:30'
    
    DECLARE @exitcode INT
    DECLARE @sqlerrorcode INT
    EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [MyDatabase] 
    FROM DISK = ''E:\Backups from elsewhere to Restore to Server\MyDatabase-post-load.sqb'' 
    WITH RECOVERY, DISCONNECT_EXISTING, 
    MOVE ''MyDatabase_Data'' TO ''E:\SQL Data\SQL2K5_Test\MyDatabase_Data.mdf'', 
    MOVE ''MyDatabase_log'' TO ''E:\SQL Data\SQL2K5_Test\MyDatabase_log.ldf'', 
    REPLACE"', @exitcode OUTPUT, @sqlerrorcode OUTPUT
    
    IF ( @exitcode <> 0 )
        OR ( @sqlerrorcode <> 0 )
        RAISERROR ('MyDatabase restore job failed with exitcode: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
    
    ALTER DATABASE [MyDatabase] SET RECOVERY SIMPLE
    GO
    
    ALTER DATABASE [MyDatabase] SET MULTI_USER
    GO
    
    USE [MyDatabase]
    go
    
    CHECKPOINT
    go
    
    sp_change_users_login 'update_one', 'Statements', 'Statements' 
    GO
    
    sp_change_users_login 'update_one', 'PublisherReports', 'PublisherReports' 
    GO
     
    BACKUP LOG [MyDatabase] WITH NO_LOG, STATS=10
    DBCC SHRINKFILE (N'MyDatabase_Log' , 1)
    
  • Options
    peteypetey Posts: 2,358 New member
    Internally, SQL Backup uses 2 connections to SQL Server, which is why SQL Server raised the error re. too many connections.

    If you use the DISCONNECT_EXISTING option in SQL Backup, SQL Backup will also use the ALTER DATABASE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE command, before setting the database back to multi-user and proceeding with the restore. Try using this option, and also setting the database back to multi-user mode prior to running the SQL Backup command, if you want to make doubly sure that all other existing connections have been disconnected e.g.
    ALTER DATABASE [MyDatabase] SET MULTI_USER 
    GO 
    
    ALTER DATABASE [MyDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
    GO 
    
    WAITFOR DELAY '00:00:30' 
    GO
    
    ALTER DATABASE [MyDatabase] SET MULTI_USER 
    GO 
    
    DECLARE @exitcode INT 
    DECLARE @sqlerrorcode INT 
    EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [MyDatabase] 
    FROM DISK = ''E:\Backups from elsewhere to Restore to Server\MyDatabase-post-load.sqb'' 
    WITH RECOVERY, DISCONNECT_EXISTING, 
    MOVE ''MyDatabase_Data'' TO ''E:\SQL Data\SQL2K5_Test\MyDatabase_Data.mdf'', 
    MOVE ''MyDatabase_log'' TO ''E:\SQL Data\SQL2K5_Test\MyDatabase_log.ldf'', 
    REPLACE, DISCONNECT_EXISTING"', @exitcode OUTPUT, @sqlerrorcode OUTPUT 
    
    IF ( @exitcode <> 0 ) 
        OR ( @sqlerrorcode <> 0 ) 
        RAISERROR ('MyDatabase restore job failed with exitcode: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode) 
    
    ALTER DATABASE [MyDatabase] SET RECOVERY SIMPLE 
    GO 
    
    ALTER DATABASE [MyDatabase] SET MULTI_USER 
    GO 
    
    USE [MyDatabase] 
    go 
    
    CHECKPOINT 
    go 
    
    sp_change_users_login 'update_one', 'Statements', 'Statements' 
    GO 
    
    sp_change_users_login 'update_one', 'PublisherReports', 'PublisherReports' 
    GO 
      
    BACKUP LOG [MyDatabase] WITH NO_LOG, STATS=10 
    DBCC SHRINKFILE (N'MyDatabase_Log' , 1)
    
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Thanks for your reply Peter, but unfortunately those changes don't seem to have had the desired effect.

    When we run the script now, we are given the following messages...
    Msg 0, Level 11, State 0, Line 0
    A severe error occurred on the current command. The results, if any, should be discarded.
    Msg 0, Level 20, State 0, Line 0
    A severe error occurred on the current command. The results, if any, should be discarded.
  • Options
    peteypetey Posts: 2,358 New member
    Could you please run each discrete command individually, and let me know which command is causing the error?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Hi Peter,

    Sorry for the delay in reply.

    The script does actually work... my mistake. The first time I ran it the database had switched from [Master] to [MyDatabase]!

    Thank you so much for your advice in resolving this. You've been a big help.

    Solved! :)

    Best wishes,
    J
  • Options
    I just hit this error - someone had removed the encryption key from the backup - my restore job was trying to apply an encryption key to an unencrypted database
Sign In or Register to comment.