RESTORE msdb FROM FULL BACKUP... HOW?

• 
  I don't work for Red Gate
  
  Can you post the actual script?
  
  I would suggest doing it manually with script inside a SSMS window
  
  make sure msdb is not in use before the restore either
  e.g.
  ALTER DATABASE msdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE

  jerryhung November 20, 2009 15:25

  - 0 +
• 
  I was simply attempting to restore from a backup over the msdb database with the GUI.
  
  And while I appreciate the willingness to help; I'd really like to hear from someone at Red Gate on this as it is a system database and their product.
  
  I'd like to know exactly how they recommend doing this.
  
  Regards
  
  Randy

  randyv November 23, 2009 07:42

  - 0 +
• 
  Hello,
  
  You may have selected the "disconnect users from database" option as part of the restore because that works by taking the database offline.
  
  The reason why the database can't be taken offline, though, is a condition of the SQL Server. Maybe you just can't do this to msdb while the server is in use.

  Brian Donahue November 23, 2009 09:19

  - 0 +
• 
  Brian -
  
  I understand. That said, I am using SQL Backup. So how would I restored the msdb?
  
  Surely Red Gate has tested this procedure. There must be some document that explains how to handle this, including some script to issue to the SQL Backup engine.
  
  Yes/No?

  randyv November 23, 2009 09:23

  - 0 +
• 
  Hi Randy,
  
  SQL Backup is a layer over SQL Server backup/restore. If you are getting a "SQL error", then there is probably something wrong with the database being restored over and we have no control over this.

  Brian Donahue November 23, 2009 09:29

  - 0 +
• 
  Brian -
  
  Understood. Let me re-phrase. I am not 'saavy' on using the scripting part of SQL Backup. So here is my question re-phrased.
  
  Assume I put SQL Server into single user mode; theoretically, now msdb can be restored. Regardless of whether restoration occurs by restoring over the existing db, or detaching and removing it and restoring from backup, the question on the table at that point is what is the scripting command to SQL Backup to have it restore from a backup file?

  randyv November 23, 2009 09:37

  - 0 +
• 
  exec master..sqlbackup '-SQL "RESTORE DATABASE [msdb] FROM DISK=[c:\backups\file.sqb] WITH PASSWORD=''password'', REPLACE"'

  Brian Donahue November 23, 2009 09:41

  - 0 +
• 
  I am trying to restore MSDB taken from SQL server 2005 to SQL server 2008.
  And I get this error
  
  SQL error 3168: SQL error 3168: The backup of the system database on the device SQLBACKUP_34307510-7656-4B6D-A024-62B5D8910432 cannot be restored because it was created by a different version of the server (9.00.1399) than this server (10.50.4000).
  
  SQL Backup exit code: 790
  SQL error code: 3168
  
  Is there a way to bypass this error?

  dananos September 19, 2013 06:18

  - 0 +
• 
  You might want to try the suggestion here, which involves:
  
  - setting up a new instance of SQL Server 2005
  - restoring the backup to that instance
  - upgrading that instance to SQL Server 2008
  - backing up the msdb database on that instance
  - restoring that backup on your target SQL Server 2008 instance

  petey September 21, 2013 21:45

  - 0 +

Add comment

Please sign in to leave a comment.