0

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?

Improve this question
1
  • Compatibility level is only for T-SQL, you don't need to for the upgrade. Make sure you have a backup :) in case something goes wrong in your inline upgrade.
    – RayofCommand
    Jan 13, 2015 at 12:42

1 Answer 1

Reset to default
2

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: Backup / Restore; change compatibility level.
  • Option 2: Detach, copy, Attach; change compatibility level.

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.

Improve this answer
1
  • 1
    I really try to discourage detach/attach. Just dangerous. If you care about your data, you're already taking backups, so you already have a backup to restore. If you detach your database because you don't have backups, and then something happens to the file, you now have ZERO copies of your database.
    – Aaron Bertrand
    Jan 13, 2015 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.