RESTORE DB FROM 2008 to 2012

Question

RESTORE DB FROM 2008 to 2012

mp5387

Right there with Babe

Points: 794
More actions
June 24, 2014 at 9:05 pm

#287150

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!!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 1

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
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

george sibbald SSC Guru Points: 104210 More actions · Answer 2

....and tempdb cannot be backed up anyway as it is recreated on SQL server restart.

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

free_mascot One Orange Chip Points: 27169 More actions · Answer 3

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."

Lowell SSC Guru Points: 323518 More actions · Answer 4

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mp5387 Right there with Babe Points: 794 More actions · Answer 5

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

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 6

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
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

EdVassie SSC Guru Points: 60472 More actions · Answer 7

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: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara