Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Restore MSDB 10.0 on a 10.5 SQL Server
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Aged Yak Warrior

596 Posts

Posted - 11/22/2013 :  11:31:58  Show Profile  Reply with Quote
I am having trouble restoring a backup of an msdb Database from a 10.0 SQL Server onto a 10.5 (i.e. 2008 R2) Server. Since I am not downgrading, I am upgrading, I should not have a problem.

Any ideas?

I need all my jobs ferom the 2008 server moved to the 2008 R2 server.

Most Valuable Yak

15732 Posts

Posted - 11/22/2013 :  14:40:28  Show Profile  Visit robvolk's Homepage  Reply with Quote
Better to script them out rather than back up and restore. Restoring system databases isn't the same as user DBs and shouldn't be done for a version upgrade.
Go to Top of Page

Starting Member

15 Posts

Posted - 11/25/2013 :  00:53:54  Show Profile  Reply with Quote
System databases can not be restored on server with different build
i.e. your source & destination servers should have same version, service pack & hotfix.

hope this helps..

Praveen D'sa
MCITP - Database Administrator 2008
Go to Top of Page

Aged Yak Warrior

596 Posts

Posted - 11/25/2013 :  11:53:03  Show Profile  Reply with Quote
Thanks everyone. I am surprised you can't restore up. I wish SQL Server could indentify that you are doing an upgrade and could make the nessessary Database changes to do that, such as adding any additional columns.
Go to Top of Page


United Kingdom
22859 Posts

Posted - 11/25/2013 :  18:13:30  Show Profile  Reply with Quote
But ... even if it could add those new columns how would it populate them with the data that would have been generated at the time that backups were made? If you would agree that any data in MSDB, created after the date/time of the upgrade, can reasonably be required to contain data in any such new columns then I reckon it becomes impossible to provide an up-restore method. Sadly.
Go to Top of Page

Almighty SQL Goddess

38200 Posts

Posted - 11/25/2013 :  18:47:46  Show Profile  Visit tkizer's Homepage  Reply with Quote
Well if you do an in-place upgrade, it does handle this scenario. So I am not sure why it can't just provide the option in the restore.

And by in-place upgrade, I mean where you have an existing instance that you upgrade to a newer version BUT NOT using a new instance. It takes the existing instance and upgrades it.

It's a risky thing if you are doing this in production (IMO), but it's one that we've used a few times recently in non-prod. Meant we didn't have to bother with scripting logins, jobs, etc. It just took care of everything.

Tara Kizer
SQL Server MVP since 2007
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000