SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
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
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

denis_the_thief
Aged Yak Warrior

Canada
593 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.

robvolk
Most Valuable Yak

USA
15676 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

Prav4u
Starting Member

India
15 Posts

Posted - 11/25/2013 :  00:53:54  Show Profile  Reply with Quote
Hi,
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
http://sqlerrors.wordpress.com
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
593 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

Kristen
Test

United Kingdom
22403 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

tkizer
Almighty SQL Goddess

USA
36997 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
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.17 seconds. Powered By: Snitz Forums 2000