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?

  • 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 '15 at 12:42
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.

  • 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 '15 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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