Solved

2005 backupfile to be restored into 2012

Posted on 2014-09-24
8
186 Views
Last Modified: 2014-10-07
a)
I am not able to take a sql backup file from
Microsoft SQL Server 2005 - 9.00.5057.00 (Intel X86)
and restore to
Microsoft SQL Server 2012 - 11.0.5058.0 (X64)

is the reason because of 32bit vs 64? or version difference or both?

b)
if I had a
2008 R2 SP2 64bit instance available, would I be able to restore that back file there without issues?
0
Comment
Question by:25112
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 25

Accepted Solution

by:
Mohammed Khawaja earned 167 total points
ID: 40342563
You cannot because 2005 compatibility is not supported in 2012.  What you need to do is restore it in SQL 2008 and change compatibility level to 100 by running the following query against the database:

ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL =100

Make a backup from SQL 2008 and restore it on SQL 2012.  Below is a table of compatibility level:

80 = SQL Server 2000
90 = SQL Server 2005
100 = SQL Server 2008 & 2008 R2
110 = SQL Server 2012
120 = SQL Server 2014

Refer to following MS link for more info:

http://msdn.microsoft.com/en-us/library/bb510680.aspx
0
 
LVL 5

Author Comment

by:25112
ID: 40342570
>> What you need to do is restore it in SQL 2008 and change compatibility level to 100

ok.. let me try that..

so the 32/64 does not affect Backup/Restore at all? (64 bit instance  backups onto 32 instance and vice versa)
0
 
LVL 5

Author Comment

by:25112
ID: 40342578
well, I tried to restore it on 2008R2.. I still get same error
~~~
Specified cast is not valid.
~~~
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 24

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 166 total points
ID: 40343218
that error does not pertain to the backup/restore process.  What is the command you are using to restore the database?
0
 
LVL 50

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 167 total points
ID: 40343592
You cannot because 2005 compatibility is not supported in 2012.
Yes it is supported. SQL Server 2012 doesn't support SQL Server 2000 and previous versions.
is the reason because of 32bit vs 64? or version difference or both?
No.
that error does not pertain to the backup/restore process.  What is the command you are using to restore the database?
I'm with dbaduck. You need to provide us more information on how are you doing the backup and restore.
0
 
LVL 5

Author Comment

by:25112
ID: 40343764
i am doing both backup and restore through GUI. let me try with script and see the results.. will post back.
0
 
LVL 25

Expert Comment

by:Mohammed Khawaja
ID: 40343776
Provide the error message.  I am thinking your database collation could be different and thus, that might be causing your problems when trying to restore.
0
 
LVL 5

Author Comment

by:25112
ID: 40365888
thank you- you were right.. the backup was corrupt the first time. next time worked. thanks a lot!
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS SQL Conditional WHERE clause 3 49
Replication failure 1 31
When to use a Unique Index? A Clustered Index? 5 76
SQL Query Across Multiple Tables - Help 5 46
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question