Could anyone please help me understand MSSQL Compatibility Level?
I have a task to upgrade a database from SQL Server 2005 to SQL Server 2012.
So I took backup of the old database and simply restored it onto SQL Server 2012 (SP3).
Executed query on New database:
SELECT compatibility_level
FROM sys.databases
WHERE name = 'MyNewDatabase';
Result:
compatibility_level
-------------------
90
But System db's are showing Compatibility_Level as '110' of MSSQL-Server-2012.
Let me conclude my problems with below questions:
If Compatibility_level =
90is for SQL Server 2005, then has the database really been upgraded to SQL Server 2012 or not?Why does the restored database show Compatibility_Level as '
90' and not '110'?Also, if it is only contains features of SQL Server 2005, then what is the better way to upgrade it to SQL Server 2012?
Is the upgrade as simple as "ALTER DB"? Like this:
ALTER DATABASE MyNewDatabase
SET COMPATIBILITY_LEVEL = '110';
sql-server sql-server-2012 sql-server-2005 upgrade compatibility-level
edited 1 min ago
jadarnel27
4,3461432
asked Dec 11 '18 at 1:45
im_oneim_one
1177
add a comment |
I have a task to upgrade a database from SQL Server 2005 to SQL Server 2012.
So I took backup of the old database and simply restored it onto SQL Server 2012 (SP3).
Executed query on New database:
SELECT compatibility_level
FROM sys.databases
WHERE name = 'MyNewDatabase';
Result:
compatibility_level
-------------------
90
But System db's are showing Compatibility_Level as '110' of MSSQL-Server-2012.
Let me conclude my problems with below questions:
If Compatibility_level =
90is for SQL Server 2005, then has the database really been upgraded to SQL Server 2012 or not?Why does the restored database show Compatibility_Level as '
90' and not '110'?Also, if it is only contains features of SQL Server 2005, then what is the better way to upgrade it to SQL Server 2012?
Is the upgrade as simple as "ALTER DB"? Like this:
ALTER DATABASE MyNewDatabase
SET COMPATIBILITY_LEVEL = '110';
sql-server sql-server-2012 sql-server-2005 upgrade compatibility-level
edited 1 min ago
jadarnel27
4,3461432
asked Dec 11 '18 at 1:45
im_oneim_one
1177
1
Also check my answer here for some distinctions between features and areas that may or may not be affected by the compatibility level vs instance version.
– LowlyDBA
Dec 11 '18 at 20:48
add a comment |
I have a task to upgrade a database from SQL Server 2005 to SQL Server 2012.
So I took backup of the old database and simply restored it onto SQL Server 2012 (SP3).
Executed query on New database:
SELECT compatibility_level
FROM sys.databases
WHERE name = 'MyNewDatabase';
Result:
compatibility_level
-------------------
90
But System db's are showing Compatibility_Level as '110' of MSSQL-Server-2012.
Let me conclude my problems with below questions:
If Compatibility_level =
90is for SQL Server 2005, then has the database really been upgraded to SQL Server 2012 or not?Why does the restored database show Compatibility_Level as '
90' and not '110'?Also, if it is only contains features of SQL Server 2005, then what is the better way to upgrade it to SQL Server 2012?
Is the upgrade as simple as "ALTER DB"? Like this:
ALTER DATABASE MyNewDatabase
SET COMPATIBILITY_LEVEL = '110';
sql-server sql-server-2012 sql-server-2005 upgrade compatibility-level
edited 1 min ago
jadarnel27
4,3461432
asked Dec 11 '18 at 1:45
im_oneim_one
1177
I have a task to upgrade a database from SQL Server 2005 to SQL Server 2012.
So I took backup of the old database and simply restored it onto SQL Server 2012 (SP3).
Executed query on New database:
SELECT compatibility_level
FROM sys.databases
WHERE name = 'MyNewDatabase';
Result:
compatibility_level
-------------------
90
But System db's are showing Compatibility_Level as '110' of MSSQL-Server-2012.
Let me conclude my problems with below questions:
If Compatibility_level =
90is for SQL Server 2005, then has the database really been upgraded to SQL Server 2012 or not?Why does the restored database show Compatibility_Level as '
90' and not '110'?Also, if it is only contains features of SQL Server 2005, then what is the better way to upgrade it to SQL Server 2012?
Is the upgrade as simple as "ALTER DB"? Like this:
ALTER DATABASE MyNewDatabase
SET COMPATIBILITY_LEVEL = '110';
sql-server sql-server-2012 sql-server-2005 upgrade compatibility-level
sql-server sql-server-2012 sql-server-2005 upgrade compatibility-level
edited 1 min ago
jadarnel27
4,3461432
asked Dec 11 '18 at 1:45
im_oneim_one
1177
edited 1 min ago
jadarnel27
4,3461432
asked Dec 11 '18 at 1:45
im_oneim_one
1177
edited 1 min ago
jadarnel27
4,3461432
edited 1 min ago
jadarnel27
4,3461432
edited 1 min ago
jadarnel27
4,3461432
4,3461432
asked Dec 11 '18 at 1:45
im_oneim_one
1177
asked Dec 11 '18 at 1:45
im_oneim_one
1177
asked Dec 11 '18 at 1:45
im_oneim_one
1177
1177
1
Also check my answer here for some distinctions between features and areas that may or may not be affected by the compatibility level vs instance version.
– LowlyDBA
Dec 11 '18 at 20:48
add a comment |
1
Also check my answer here for some distinctions between features and areas that may or may not be affected by the compatibility level vs instance version.
– LowlyDBA
Dec 11 '18 at 20:48
1
1
Also check my answer here for some distinctions between features and areas that may or may not be affected by the compatibility level vs instance version.
– LowlyDBA
Dec 11 '18 at 20:48
Also check my answer here for some distinctions between features and areas that may or may not be affected by the compatibility level vs instance version.
– LowlyDBA
Dec 11 '18 at 20:48
add a comment |
1 Answer
1
active
oldest
votes
Maybe Compatibility_level = 90 is of MSSQL-2005, then is it really been upgraded to MSSQL-Server-2012 or not? how may I know?
If it's running on the 2012 instance, then that database's metadata has been upgraded. So you're good.
Could anyone please explain me why restored database also showing Compatibility_Level as '90' and not '110'?
When you restore a database onto a newer version (called an upgrade), it's compatibility level is set to the lowest supported version for that instance. In this case, 90 is still functional on the 2012 instance, so it was left as-is.
Also, If it is still contains featured of MSSQL-Server-2005 only, then what is the better way to upgrade it to MSSQL-Server-2012?
Upgrade is as much simple as "ALTER DB"?? like
Yes, it really is just as easy as ALTER DATABASE ... SET COMPATIBILITY_LEVEL.
You can remain at the lower compatibility level as long as you like. However, to use newer features introduced since version 90, you'd need to update to the later compatibility levels. You can see a list of some of the changes here:
Differences Between Lower Compatibility Levels and Levels 110 and 120
Also note that, starting with SQL Server 2014 (level 120), the 90 compatibility level is not available. So the next time you upgrade, your database will automatically be updated to compatibility level 100. You can see a table of supported levels on each engine version here:
ALTER DATABASE (Transact-SQL) Compatibility Level - Arguments
answered Dec 11 '18 at 2:20
jadarnel27jadarnel27
4,3461432
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f224602%2fcould-anyone-please-help-me-understand-mssql-compatibility-level%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Maybe Compatibility_level = 90 is of MSSQL-2005, then is it really been upgraded to MSSQL-Server-2012 or not? how may I know?
If it's running on the 2012 instance, then that database's metadata has been upgraded. So you're good.
Could anyone please explain me why restored database also showing Compatibility_Level as '90' and not '110'?
When you restore a database onto a newer version (called an upgrade), it's compatibility level is set to the lowest supported version for that instance. In this case, 90 is still functional on the 2012 instance, so it was left as-is.
Also, If it is still contains featured of MSSQL-Server-2005 only, then what is the better way to upgrade it to MSSQL-Server-2012?
Upgrade is as much simple as "ALTER DB"?? like
Yes, it really is just as easy as ALTER DATABASE ... SET COMPATIBILITY_LEVEL.
You can remain at the lower compatibility level as long as you like. However, to use newer features introduced since version 90, you'd need to update to the later compatibility levels. You can see a list of some of the changes here:
Differences Between Lower Compatibility Levels and Levels 110 and 120
Also note that, starting with SQL Server 2014 (level 120), the 90 compatibility level is not available. So the next time you upgrade, your database will automatically be updated to compatibility level 100. You can see a table of supported levels on each engine version here:
ALTER DATABASE (Transact-SQL) Compatibility Level - Arguments
answered Dec 11 '18 at 2:20
jadarnel27jadarnel27
4,3461432
add a comment |
Maybe Compatibility_level = 90 is of MSSQL-2005, then is it really been upgraded to MSSQL-Server-2012 or not? how may I know?
If it's running on the 2012 instance, then that database's metadata has been upgraded. So you're good.
Could anyone please explain me why restored database also showing Compatibility_Level as '90' and not '110'?
When you restore a database onto a newer version (called an upgrade), it's compatibility level is set to the lowest supported version for that instance. In this case, 90 is still functional on the 2012 instance, so it was left as-is.
Also, If it is still contains featured of MSSQL-Server-2005 only, then what is the better way to upgrade it to MSSQL-Server-2012?
Upgrade is as much simple as "ALTER DB"?? like
Yes, it really is just as easy as ALTER DATABASE ... SET COMPATIBILITY_LEVEL.
You can remain at the lower compatibility level as long as you like. However, to use newer features introduced since version 90, you'd need to update to the later compatibility levels. You can see a list of some of the changes here:
Differences Between Lower Compatibility Levels and Levels 110 and 120
Also note that, starting with SQL Server 2014 (level 120), the 90 compatibility level is not available. So the next time you upgrade, your database will automatically be updated to compatibility level 100. You can see a table of supported levels on each engine version here:
ALTER DATABASE (Transact-SQL) Compatibility Level - Arguments
answered Dec 11 '18 at 2:20
jadarnel27jadarnel27
4,3461432
add a comment |
Maybe Compatibility_level = 90 is of MSSQL-2005, then is it really been upgraded to MSSQL-Server-2012 or not? how may I know?
If it's running on the 2012 instance, then that database's metadata has been upgraded. So you're good.
Could anyone please explain me why restored database also showing Compatibility_Level as '90' and not '110'?
When you restore a database onto a newer version (called an upgrade), it's compatibility level is set to the lowest supported version for that instance. In this case, 90 is still functional on the 2012 instance, so it was left as-is.
Also, If it is still contains featured of MSSQL-Server-2005 only, then what is the better way to upgrade it to MSSQL-Server-2012?
Upgrade is as much simple as "ALTER DB"?? like
Yes, it really is just as easy as ALTER DATABASE ... SET COMPATIBILITY_LEVEL.
You can remain at the lower compatibility level as long as you like. However, to use newer features introduced since version 90, you'd need to update to the later compatibility levels. You can see a list of some of the changes here:
Differences Between Lower Compatibility Levels and Levels 110 and 120
Also note that, starting with SQL Server 2014 (level 120), the 90 compatibility level is not available. So the next time you upgrade, your database will automatically be updated to compatibility level 100. You can see a table of supported levels on each engine version here:
ALTER DATABASE (Transact-SQL) Compatibility Level - Arguments
answered Dec 11 '18 at 2:20
jadarnel27jadarnel27
4,3461432
Maybe Compatibility_level = 90 is of MSSQL-2005, then is it really been upgraded to MSSQL-Server-2012 or not? how may I know?
If it's running on the 2012 instance, then that database's metadata has been upgraded. So you're good.
Could anyone please explain me why restored database also showing Compatibility_Level as '90' and not '110'?
When you restore a database onto a newer version (called an upgrade), it's compatibility level is set to the lowest supported version for that instance. In this case, 90 is still functional on the 2012 instance, so it was left as-is.
Also, If it is still contains featured of MSSQL-Server-2005 only, then what is the better way to upgrade it to MSSQL-Server-2012?
Upgrade is as much simple as "ALTER DB"?? like
Yes, it really is just as easy as ALTER DATABASE ... SET COMPATIBILITY_LEVEL.
You can remain at the lower compatibility level as long as you like. However, to use newer features introduced since version 90, you'd need to update to the later compatibility levels. You can see a list of some of the changes here:
Differences Between Lower Compatibility Levels and Levels 110 and 120
Also note that, starting with SQL Server 2014 (level 120), the 90 compatibility level is not available. So the next time you upgrade, your database will automatically be updated to compatibility level 100. You can see a table of supported levels on each engine version here:
ALTER DATABASE (Transact-SQL) Compatibility Level - Arguments
answered Dec 11 '18 at 2:20
jadarnel27jadarnel27
4,3461432
edited Dec 11 '18 at 16:06
answered Dec 11 '18 at 2:20
jadarnel27jadarnel27
4,3461432
answered Dec 11 '18 at 2:20
jadarnel27jadarnel27
4,3461432
answered Dec 11 '18 at 2:20
jadarnel27jadarnel27
4,3461432
4,3461432
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f224602%2fcould-anyone-please-help-me-understand-mssql-compatibility-level%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Also check my answer here for some distinctions between features and areas that may or may not be affected by the compatibility level vs instance version.
– LowlyDBA
Dec 11 '18 at 20:48