Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

This question already has an answer here:

I am working on different two SQL Server, I need to transfer database from one server to another but problem is the servers have different versions.

Below is image of error message.

enter image description here

Using SELECT @@Version I have checked database version like below,

First Database Version (Source Database):

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4260.0 (X64)   
Jul 11 2012 15:47:13   Copyright (c) Microsoft Corporation  Web Edition (64-bit) 
on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor) 

Second Database Version (Destination Database):

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64)   
Jul  9 2008 14:17:44   Copyright (c) 1988-2008 Microsoft Corporation 
Enterprise Edition (64-bit) 
on Windows NT 6.0 <X64> (Build 6001: Service Pack 1) (VM)

How can I do this?

share|improve this question

migrated from May 1 '13 at 10:07

This question came from our site for professional and enthusiast programmers.

marked as duplicate by Aaron Bertrand, StanleyJohns, Marian, Mat, Mark Storey-Smith May 20 '13 at 13:47

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

I don't think you can directly restore a back up from SQL Server 2008 R2 to SQL Server 2008. Here is a similar question. – Kaf May 1 '13 at 9:00
what is alternative ? – Anvesh May 1 '13 at 9:02
You can create a script and run it on 2008 server. Then import the data using Import Export Wizard. – Kaf May 1 '13 at 9:09

+1 to Doug L., I'd recommend the same (export all data and structure into SQL script and data dumps, then import) but on top of that: if you can connect the two DBs you can cross-server SELECT/INSERT INTO the tables and data from old DB to the new one; you can also try upgrading the RTM server (if you are allowed to do that) - I'm unsure on this but I think if you install SP on the RTM it'll be considered as same version as the other guy.

share|improve this answer
I am moving whole database , this is not about data import / export. – Anvesh May 1 '13 at 9:20
Exactly. And you have a problem with that which you should workaround one way or another and asked a question as you can't sort it out by yourself. All the possible options are in my answer, can't think anything else (compatibility in my experience don't help). So yes "Anu" this is exactly about export/import of structure and data..... – Mark May 1 '13 at 9:30
destination has no any database I would like restore , so import / export is not solution . – Anvesh May 1 '13 at 9:34
@Anu A Google search shows it is the only solution. If you script the database, running the script on the target server will recreate the database. It matters not that there is no database on the target server. Mark Kondor suggested exporting structure and data, not just data. So this would work. Take a look at:… – Ɖiamond ǤeezeƦ May 1 '13 at 9:38
+1 to Mark. The down vote was unfair. – Ɖiamond ǤeezeƦ May 1 '13 at 9:39
  1. You could change the compatibility setting on the database on the first server, backup it up and then restore to the second database server [We now know this won't work as commented on below].

  2. You could use third party tools like Redgate's SQL Compare Pro and SQL Data Compare Pro.

  3. Upgrade the second database server.

share|improve this answer
My bad. Have updated answer with another suggestion. – Ɖiamond ǤeezeƦ May 1 '13 at 9:03
how can I change compatibility of server – Anvesh May 1 '13 at 9:08
I don't know about changing compatibility of the server. You can change the compatibility of individual databases by right-clicking on a database, selecting Properties and changing the compatibility there. However, I suspect that it is the format of the backup file that is incompatible rather than the database. But still worth a go. – Ɖiamond ǤeezeƦ May 1 '13 at 9:13
Compatibility setting doesn't affect the version stored in the mdf file, which is what ends up in the backup. – Damien_The_Unbeliever May 1 '13 at 9:21
@Damien_The_Unbeliever +1 - I suspected as much. Thanks. – Ɖiamond ǤeezeƦ May 1 '13 at 9:23

In the SQL Server Management Studio, right-click your source database, select Tasks, Generate Scripts. Use this to generate the SQL script that will create and populate the objects in the database. There will be several options for selecting the objects, selecting data, targeting a server version, desired output, etc. The resulting script can be run on the target server.

share|improve this answer

Do not be tempted to use the Copy Database tool within SSMS: it is a massive pain in the behind and requires a LOT of messing about to make it work thanks to its use of SSIS. It should be simple, and indeed it was in SQL Server 2000, but it requires so much security faffing now that it's not really worth the effort. I've lost countless hours trying to make it work reliably.

It's not the easiest or cleanest solution, but I would agree with the previous posters that scripting the entire DB out is your best bet IF you don't have a large DB. If you do then realistically your only option is to upgrade the target server to 2008 R2 (or even 2012). Bear in mind that the Developer Edition of any given SQL Server version is only about £50 (about US$77 right now, and may actually be less than that in US$ markets) and is functionally equivalent to the full Enterprise Edition.

If you're really stuck and can't get the cash, you may be able to use the latest Express Edition (free) and restore to that but check that there is no limited functionality which might impede your development. I could be wrong but I think the Express editions have most of the features of Standard edition but limit DB size and possibly number of connections. You already have SSMS so you can connect straight to your new Express instance.

As an aside, I think MS have put some effort into making the Copy Database wizard better and easier to use in 2012, but I've not tried it yet.

Incidentally compatibility levels have nothing to do with DB versions, only restricting what features can be used on a specific DB. For example, you might want to prevent the use of CLR functions in a DB because it's an old legacy system so you might have the compatibility level set to 80 to prevent anyone adding such features to the DB.

share|improve this answer
Why the driveby -1? If you have an issue with the answer at least explain why. – Steve Pettifer May 1 '13 at 15:29

Not the answer you're looking for? Browse other questions tagged or ask your own question.