Solved

2005 backupfile to be restored into 2012

Posted on 2014-09-24
8
172 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
8 Comments
 
LVL 24

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
 
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 45

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 24

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video discusses moving either the default database or any database to a new volume.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

762 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now