Solved

2005 backupfile to be restored into 2012

Posted on 2014-09-24
8
184 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 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 48

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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 Delete Query 9 37
TSQL Challenge... 7 36
SSRS: Why is Visual Studio stripping these properties? 2 22
Stored Procedure needs owner to execute 5 19
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

838 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