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

• 
  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.

  petey June 09, 2014 20:44

  - 0 +
• 
  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)
  

  sonnydeletejc June 10, 2014 04:18

  - 0 +
• 
  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)
  

  petey June 12, 2014 03:13

  - 0 +
• 
  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.

  sonnydeletejc June 12, 2014 05:46

  - 0 +
• 
  Could you please run each discrete command individually, and let me know which command is causing the error?
  
  Thanks.

  petey June 12, 2014 10:34

  - 0 +
• 
  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

  sonnydeletejc June 13, 2014 04:40

  - 0 +
• 
  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

  Mike_Vessey77 September 10, 2019 15:20

  - 0 +

Add comment

Please sign in to leave a comment.