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)
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.