What is the difference between SQL Server databases A & B where:

A. Database restored from lower level compatibility(100) to higher compatibility (130)

B. New Database created in higher compatibility (130)

  • No difference as ultimately both the databases are on compatibility level 130, how they reached there should not be a matter of concern. – Shanky Aug 24 '18 at 9:37

I'm not sure if this gets to the root of your question - if not, please update your question with more information.

According the the documentation for ALTER DATABASE (Transact-SQL) Compatibility Level, the compatibility mode of a database:

Sets certain database behaviors to be compatible with the specified version of SQL Server.

As I understand the documentation, two databases running under the same compatibility mode should operate the same way with regard to those specific behaviors.

Restored databases do not (necessarily) automatically change their compatibility mode.

From the documentation:

When a database is upgraded from any earlier version of SQL Server, the database retains its existing compatibility level, if it is at least minimum allowed for that instance of SQL Server. Upgrading a database with a compatibility level lower than the allowed level, automatically sets the database to the lowest compatibility level allowed. This applies to both system and user databases.

| improve this answer | |

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.