Solved

2005 backupfile to be restored into 2012

Posted on 2014-09-24
8
188 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

707 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