RESTORE msdb FROM FULL BACKUP... HOW?

Compresses, encrypts, secures and monitors SQL Server backups.

RESTORE msdb FROM FULL BACKUP... HOW?

Postby randyv » Fri Nov 20, 2009 5:16 pm

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
randyv
 
Posts: 113
Joined: Wed Feb 28, 2007 3:27 pm

Postby jerryhung » Fri Nov 20, 2009 9:25 pm

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
jerryhung
 
Posts: 113
Joined: Fri Dec 28, 2007 5:12 pm

Was using the GUI

Postby randyv » Mon Nov 23, 2009 1:42 pm

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
randyv
 
Posts: 113
Joined: Wed Feb 28, 2007 3:27 pm

Postby Brian Donahue » Mon Nov 23, 2009 3:19 pm

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
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

So how do I restore msdb?

Postby randyv » Mon Nov 23, 2009 3:23 pm

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
randyv
 
Posts: 113
Joined: Wed Feb 28, 2007 3:27 pm

Postby Brian Donahue » Mon Nov 23, 2009 3:29 pm

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
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

worded the question poorly

Postby randyv » Mon Nov 23, 2009 3:37 pm

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
randyv
 
Posts: 113
Joined: Wed Feb 28, 2007 3:27 pm

Postby Brian Donahue » Mon Nov 23, 2009 3:41 pm

exec master..sqlbackup '-SQL "RESTORE DATABASE [msdb] FROM DISK=[c:\\backups\\file.sqb] WITH PASSWORD=''password'', REPLACE"'
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

SQL error 3168: SQL error 3168:

Postby dananos » Thu Sep 19, 2013 11:18 am

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
 
Posts: 1
Joined: Thu Sep 19, 2013 11:15 am

Postby petey » Sun Sep 22, 2013 2:45 am

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 7
petey
 
Posts: 2234
Joined: Sun Apr 24, 2005 12:34 pm


Return to SQL Backup 6

Who is online

Users browsing this forum: No registered users and 0 guests