SQL Server 2005 and SQL Server 2008 how to make it compatible?

views:

answers:

+1 Q:

SQL Server 2005 and SQL Server 2008 how to make it compatible?

I have in production SQL Server 2005 and in Development SQL Server 2008 Database.

I would like to detach database from production create tables, insert data etc. and then attach it back.

How make it compatible after I make some changes in SQL server 2008? What is the right way to do it?

There is one option you can set, compatibility level, If I remember correctly you can set it to version 9.0 (2005) or to 10.0 (2008)

Matteo Mosca 2010-06-23 15:50:57

When I'm creating a new database in 10.0 I can specify this options. But in my case I already have sql server database 2005

George 2010-06-23 15:52:46

In 2005 you don't have to set anything. Just in 2008 to make your db 2005 compatible.

Matteo Mosca 2010-06-23 15:55:56

This **will not** allow you to detach the database from SQL2008 and use it on an SQL2005 instance though. Once attached there is no going back.

Martin Smith 2010-06-23 15:58:07

-1 Don't confuse the compatibility option with the database version. A database attached to SQL Server 2008 it changes the physical format and is no longer possible to attach or restore back on SQL Server 2005.

Remus Rusanu 2010-06-23 16:36:24

I must apologize - I never intended to mean that you can attach a sql 2008 db to sql 2005, the compatibility option I mentioned is clearly not meant for that, I should have been more clear on that. Sorry.

Matteo Mosca 2010-06-24 08:51:18

+4 A:

There is no way to do this.

The only way I know to move a database from MSSQL2008 to 2005 is to script the tables.

Once the new tables are created in 2005, you can transfer the data either by scripting it (no fun) or by the data transfer wizard.

BoltBait 2010-06-23 15:54:30

Any data for lookup tables should have been inserted from a script. Other data is probably test data and you would not want to move it to prod (but still should only be inserted from a script).

HLGEM 2010-06-23 17:33:29

+1 A:

Changes to tables and data (for lookup tables) should be scripted and in source control like any other code. Then you run the scripts for the changes you are promoting to prod. This way other dev changes which are not ready to go to prod can be held back from being sent to prod.

I also do not recommend that you have a development database in a differnt version of SQL Server unless what you are testing to see if anything needs to be changed in preparation of moving to 2008. Otherwise, it is highly likely that bad code will be written that the prodcution database cannot handle becasue it is the wrong version and you will not find this out until you send the change to production.

HLGEM 2010-06-23 17:30:10

+1 Amen to that

Remus Rusanu 2010-06-23 20:14:00

ansaurus

tags:

views:

answers:

SQL Server 2005 and SQL Server 2008 how to make it compatible?

related questions