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
· Share on Twitter

Comments

  • 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
    · Share on Twitter
  • sonnydeletejcsonnydeletejc Posts: 5
    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)
    · Share on Twitter
  • 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
    · Share on Twitter
  • sonnydeletejcsonnydeletejc Posts: 5
    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.
    · Share on Twitter
  • 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
    · Share on Twitter
  • sonnydeletejcsonnydeletejc Posts: 5
    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
    · Share on Twitter
  • Mike_Vessey77Mike_Vessey77 Posts: 7 New member
    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
    · Share on Twitter
Sign In or Register to comment.