Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 193
  • Last Modified:

2005 backupfile to be restored into 2012

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
25112
Asked:
25112
3 Solutions
 
Mohammed KhawajaManager - Infrastructure: Information TechnologyCommented:
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
 
25112Author Commented:
>> 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
 
25112Author Commented:
well, I tried to restore it on 2008R2.. I still get same error
~~~
Specified cast is not valid.
~~~
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
DBAduck - Ben MillerPrincipal ConsultantCommented:
that error does not pertain to the backup/restore process.  What is the command you are using to restore the database?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
25112Author Commented:
i am doing both backup and restore through GUI. let me try with script and see the results.. will post back.
0
 
Mohammed KhawajaManager - Infrastructure: Information TechnologyCommented:
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
 
25112Author Commented:
thank you- you were right.. the backup was corrupt the first time. next time worked. thanks a lot!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now