SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


RESTORE DB FROM 2008 to 2012


RESTORE DB FROM 2008 to 2012

Author
Message
mp5387
mp5387
SSC-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 Visits: 90
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!!
Shawn Melton
Shawn Melton
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5923 Visits: 3537
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: blog.wsmelton.info
george sibbald
george sibbald
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24692 Visits: 13698
....and tempdb cannot be backed up anyway as it is recreated on SQL server restart.

---------------------------------------------------------------------
free_mascot
free_mascot
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7501 Visits: 2250
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
Lowell
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72016 Visits: 40942
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
mp5387
SSC-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 Visits: 90
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
Shawn Melton
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5923 Visits: 3537
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: blog.wsmelton.info
EdVassie
EdVassie
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14056 Visits: 3901
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 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search