Changing Compatability level when using copy wizard

If the compatibility level in an Sql Server 2008 R2 database is changed to 2005, can the copy database wizard copy the database without any issues from 2008 R2 to 2005?

Or is Generating scripts the only way to copy a 2开发者_JAVA技巧008 R2 database to a 2005 database.

---

I don't believe you can use the Copy Wizard to downgrade your database back to 2005. However, the DataImport... task in SSMS should be able to help you move your data tables and views. (Basically it creates an SSIS package to transfer table definitions and data)

But if you want to truly move the database scripting is your best bet. Also you can check out tools from Red Gate such as SQLCompare and SQLDataCompare.

---

Just becasue a compatibility level is set to a lower value than the current version does not forbid you to use the newer features. All it does is stop enforcing the removal of features to preserve backward compatibilty of existing code. Since you can use new datatypes and features in your 2008 database, you cannot directly copy back to 2005 without analysis of what new features were used.

If you are developing in 2008 and prod is 2005, you need to stop that practice immediately. Otherwise you will waste a lot of time using new features without realizing it until you try to send to prod.

Database changes should only ever be done through scripts in general as you do not want to risk dropping and recreating tables that have existing data. Also database change scripts should be in source control for the version you are deploying like any other code. Part of making the changes in scripts is that the script you then run on prod has been tested on dev and QA.