CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Zach
Zach

Reputation: 447

How do I move data between databases using different version of SQL Server Management Studio?

I have a database deployed on a testing machine and a production machine. There is some data (columns of a table) in the testing database that I would like to copy over to production.

When I try to restore a backup onto the production box I get the following error:

System.Data.SqlClient.SqlError: The database was backed up on a server running version 10.50.1600. That version is incompatible with this server, which is running version 10.00.4000. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. (Microsoft.SqlServer.Smo)

Is there an alternate way to get the data into the table?

Upvotes: 3

Views: 6250

Answers (6)

Hiren Dhaduk
Hiren Dhaduk

Reputation: 2780

you can use redgate toolbelt tool kit to sync data between different version of sql server or sql server to sql azure or sql azure to sql server database . it is very very easy to use .

Upvotes: 0

Smaug
Smaug

Reputation: 2673

This is the problem with the sqlserver version mismatch while restoring the database. It means It couldn't possible to restore the database server 2008 to 2005. It wouldn't support higher version to lowerr version.

The following options are available,

  1. You can link the servers and move the table from one server to other server

  2. Generate the sql script and execute in the target database

Use the below URL to generate the insert script and execute generated script into target server.

http://www.codeproject.com/Articles/5598/Generating-INSERT-statements-in-SQL-Server

Upvotes: 0

steoleary
steoleary

Reputation: 9278

Ideally you'd upgrade your production server to match your test environment, otherwise your tests may not always be representative.

If they are different versions though, you can't restore a database to an older version of SQL server, you should either use the generate scripts command to create the new table and insert the data, or use a tool like SQL compare/Data Compare from RedGate which will do all of this for you.

Upvotes: 0

Kprof
Kprof

Reputation: 752

This answer ONLY if you need the data (from here: http://sqlserverlearner.com/tag/the-database-was-backed-up-on-a-server-running-version-10-50-1600-that-version-is-incompatible-with-this-server):

  • Script the object you want (table and data) under tasks, generate scripts, select the object, and make sure to include all the data (possibly the schema as well). Take this to a new query window and save the SQL file, then execute it on your other server.

Upvotes: 4

UnitStack
UnitStack

Reputation: 1185

Use generate scripts with data, you can select the version of MSSQL. Then run the outputted SQL on the server you want it.

Upvotes: 0

squillman
squillman

Reputation: 13641

You could

  • Generate INSERT statements for the data in question
  • Create a linked server from the test to the other server and run sql statements against that
  • Upgrade the target server to 2008 R2 (this is not something to take lightly)

You should keep your environments at the same version / rev. SQL Server restores are not backward compatible.

Upvotes: 0

Related Questions