I would like to move my databases which are currently running on SQL Server 2005 to SQL Server 2012, using the Copy Database Wizard in SQL Server Management Studio.
Should I first set the database compatibility level to SQL Server 2012 (while the database still exists on SQL Server 2005) or should I set the compatibility level to SQL Server 2005 after I've moved the database?
Best Answer
You can not set the compatibility level of a SQL Server 2005 instance to anything higher. Hence the upgrade path:
Create SQL Server 2012 Server / Instance.
Option 1 is most used because you have overlap (old is available, new can be tested). Rollback to previous is certain.
Misread your question. You want to remain at SQL 2005 compatibility: Don't change compatibility level during option 1 or 2. Compatibility is configured at the database level.