3

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?

Colin 't Hart
  • 9,455
  • 15
  • 36
  • 44
Anvesh
  • 131
  • 1
  • 1
  • 3

4 Answers4

3

+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.

1

  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.

Ɖiamond ǤeezeƦ
  • 146
  • 3
0

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.

Doug L.
  • 103
  • 4
0

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.

Steve Pettifer
  • 441
  • 4
  • 10