Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

RESTORE DB FROM 2008 to 2012 Expand / Collapse
Author
Message
Posted Tuesday, June 24, 2014 9:05 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 27, 2014 2:49 PM
Points: 18, Visits: 80
Hello everybody. I am going to restore my master, model, msdb, and tempdb from my previous MS sql server 2008 r2 to the new MS SQL Server 2012 r2. Is that possible, or not?
Thanks for your answer!!
Post #1585729
Posted Tuesday, June 24, 2014 9:41 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, August 17, 2014 8:51 PM
Points: 851, Visits: 2,363
You can only restore them to the same version they were backed up from. As stated in BOL:

System databases can be restored only from backups that are created on the version of SQL Server that the server instance is currently running. For example, to restore a system database on a server instance that is running on SQL Server 2005 SP1, you must use a database backup that was created after the server instance was upgraded to SQL Server 2005 SP1.


Shawn Melton
PS C:\>(Find-Me).TwitterURL
@wsmelton
PS C:\>(Find-Me).BlogURL
meltondba.wordpress.com
Post #1585730
Posted Wednesday, June 25, 2014 2:04 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:05 PM
Points: 5,989, Visits: 12,915
....and tempdb cannot be backed up anyway as it is recreated on SQL server restart.

---------------------------------------------------------------------

Post #1585776
Posted Wednesday, June 25, 2014 6:24 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
As mention above it is not possible. Why you would like to restore 2008 R2 System db to 2012? What is your objective?

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Post #1585855
Posted Wednesday, June 25, 2014 6:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:53 PM
Points: 12,903, Visits: 31,966
what you really want to do is a side by side server upgrade.
on the 2008 server, you script out stuff, and run the scripts on the new server. no restoring is involved.
i can assume you've got the backup of all the databases themselves covered, but from experience, that's not the whole puzzle:

was there a reporting service on that machine? you need to backup the encryption key, and then import that key in the new reporting services.
ditto for any Anaysis services stuff? backing up to *.abf files and restoring is pretty quick.

did you have any procedures, functions or tables in the master database? script em out.
any server triggers? script them out as well.
script out logins via sp_help_revlogin.
did you have any wierd providers(AS400?) on the original machine that was used for Linked servers? you might need to install those providers on the new machine.

script out linked servers. you have to change the script for remote logins that had passwords to the "Real" password, as that scripts out with ### signs.
script out any database mail settings.
How about Credentials that might be used for various processes? you'll need to recreate those.
tied to that, you have operators in msdb that need to be recreated.
now that you are in msdb, script out the jobs. make sure you physically copy any SSIS packages that might be called via those jobs too. if the packages are in the msdb database, you might need to extract them out to disk to redeploy them. there's a powershell script I've used for that


when you restore the databases, make sure you update statistics on all databases!


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1585861
Posted Wednesday, June 25, 2014 10:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 27, 2014 2:49 PM
Points: 18, Visits: 80
Thanks for your answer. I decided to install same sql server ( 2008 r2 ) on my new server with MS 2012 OS.
Do we need any order to restore our system database , and database for the application?
Thanks for your answer
Post #1586007
Posted Wednesday, June 25, 2014 10:45 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, August 17, 2014 8:51 PM
Points: 851, Visits: 2,363
mp5387 (6/25/2014)
Thanks for your answer. I decided to install same sql server ( 2008 r2 ) on my new server with MS 2012 OS.
Do we need any order to restore our system database , and database for the application?
Thanks for your answer

Read the documentation in Books Online regarding restoring the system databases. The link I provided is the starting link.


Shawn Melton
PS C:\>(Find-Me).TwitterURL
@wsmelton
PS C:\>(Find-Me).BlogURL
meltondba.wordpress.com
Post #1586008
Posted Monday, June 30, 2014 3:22 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:23 AM
Points: 2,867, Visits: 3,204
You should not restore system databases across different versions of SQL Server.

Each version of SQL Server has version-specific information in master, resource, and msdb. If you do somehow manage to restore these databases across SQL version and get SQL Server to start, you will have put yourself outside of Microsoft support if you do run into any problems.

You need to look at an upgrade plan that involves scripting out all objects from your old system that you need to transfer, and scripting them into your new system.


Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 28 July 2014: now over 30,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1587536
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse