Red Gate forums :: View topic - RESTORE msdb FROM FULL BACKUP... HOW?
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup 6
SQL Backup 6 forum

RESTORE msdb FROM FULL BACKUP... HOW?

Search in SQL Backup 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
randyv



Joined: 28 Feb 2007
Posts: 113

PostPosted: Fri Nov 20, 2009 5:16 pm    Post subject: RESTORE msdb FROM FULL BACKUP... HOW? Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
jerryhung



Joined: 28 Dec 2007
Posts: 116

PostPosted: Fri Nov 20, 2009 9:25 pm    Post subject: Reply with quote

I don't work for Red Gate Smile

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
Back to top
View user's profile Send private message
randyv



Joined: 28 Feb 2007
Posts: 113

PostPosted: Mon Nov 23, 2009 1:42 pm    Post subject: Was using the GUI Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
Brian Donahue



Joined: 23 Aug 2004
Posts: 6647

PostPosted: Mon Nov 23, 2009 3:19 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
randyv



Joined: 28 Feb 2007
Posts: 113

PostPosted: Mon Nov 23, 2009 3:23 pm    Post subject: So how do I restore msdb? Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
Brian Donahue



Joined: 23 Aug 2004
Posts: 6647

PostPosted: Mon Nov 23, 2009 3:29 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
randyv



Joined: 28 Feb 2007
Posts: 113

PostPosted: Mon Nov 23, 2009 3:37 pm    Post subject: worded the question poorly Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
Brian Donahue



Joined: 23 Aug 2004
Posts: 6647

PostPosted: Mon Nov 23, 2009 3:41 pm    Post subject: Reply with quote

exec master..sqlbackup '-SQL "RESTORE DATABASE [msdb] FROM DISK=[c:\backups\file.sqb] WITH PASSWORD=''password'', REPLACE"'
Back to top
View user's profile Send private message
dananos



Joined: 19 Sep 2013
Posts: 1

PostPosted: Thu Sep 19, 2013 11:18 am    Post subject: SQL error 3168: SQL error 3168: Reply with quote

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?
Back to top
View user's profile Send private message AIM Address
petey



Joined: 24 Apr 2005
Posts: 2299

PostPosted: Sun Sep 22, 2013 2:45 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group