RESTORE msdb FROM FULL BACKUP... HOW?

randyvrandyv Posts: 166
edited September 21, 2013 9:45PM in SQL Backup Previous Versions
I am trying to restore msdb from a full backup taken last night.
I have turned off SQL Agent.
Yet, I continue to get this error when trying to restore...
WHALEY07: Restore - Failed
==========================

Restoring full backup - Failed

This operation failed with errors.

Restoring msdb (database) from:
E:\FULL_(local)_msdb_20091119_200003.sqb
VDI error 1010: Failed to get the configuration from the server because the timeout interval has elapsed. Check that the SQL Server instance is running, that you have the SQL Server System Administrator server role; and that no other processes are
blocking the backup or restore process; or try increasing the value of the VDITimeout registry setting in HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\[InstanceName]
Also check that the database is not currently in use.
SQL error 5058: Option 'OFFLINE' cannot be set in database 'msdb'.
Memory profile
Type Maximum Minimum Average Blk count Total
Commit 678952960 4096 600506 1441 865329152
Reserve 339410944 4096 5596070 184 1029677056
Free 161189888 4096 1459028 173 252411904
Private 678952960 4096 1625805 1111 1806270464
Mapped 1060864 4096 94776 72 6823936
Image 32657408 4096 185320 442 81911808
SQL Backup exit code: 1010
SQL error code: 5058



Updating SQL Server information - Pending

Operation pending.


What do I need to do to restore msdb from a backup?
What we do in life echoes in eternity <><
Randy Volters
· Share on Twitter

Comments

  • jerryhungjerryhung Posts: 138
    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
    Jerry Hung
    DBA, MCITP
    · Share on Twitter
  • randyvrandyv Posts: 166
    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
    What we do in life echoes in eternity <><
    Randy Volters
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
    · Share on Twitter
  • randyvrandyv Posts: 166
    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?
    What we do in life echoes in eternity <><
    Randy Volters
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
    · Share on Twitter
  • randyvrandyv Posts: 166
    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?
    What we do in life echoes in eternity <><
    Randy Volters
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    exec master..sqlbackup '-SQL "RESTORE DATABASE [msdb] FROM DISK=[c:\backups\file.sqb] WITH PASSWORD=''password'', REPLACE"'
    · Share on Twitter
  • dananosdananos Posts: 1
    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?
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    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
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
Sign In or Register to comment.