Restoring a backup of a SQL Server 2008 (Compatibility 100) DB creates a SQL Server 2005 DB on 2012 server
We have four different databases on a SQL Server 2008 (not SP2 - don't ask me why) database server that have an odd problem.
When listed from sys.databases, these databases report that their compatibility is SQL Server 2008/R2 (compatibility level 100) as shown for these three of the DBs here:
MyDB_Name 100 SQL Server 2008/R2
MyDB_Name_MirrorTables 100 SQL Server 2008/R2
MyDB_Name_Reporting 100 SQL Server 2008/R2
As output from this query:
select
name, compatibility_level , version_name =
case compatibility_level
when 90 then 'SQL Server 2005'
when 100 then 'SQL Server 2008/R2'
when 110 then 'SQL Server 2012'
else 'unknown - ' + convert(varchar(10), compatibility_level)
end
from sys.databases
Restores from backups of other user DBs on the server restore correctly as compatibility level 100. However, restores of these four DBs from backups restore as compatibility level 90 (SQL Server 2005). Only these four DBs appear to have this issue.
To test this, I took a manual backup of one of these DBs with only the options INIT, SKIP specified. I then restored this backup to a SQL Server 2012 server. When restored, the DB went through the upgrade process from Version 655 to 706. Nothing else unusual happened.
However, when testing the compatibility level on SQL Server 2012, using the same code as above, this is the value returned:
bwh_MyDB_Name_MirrorTables 90 SQL Server 2005
Additionally, when restored to the original SQL Server 2008 DB server under a different name, the database still returns SQL Server 2005 (compatibility level 90) for the DB version.
Finally, although the DB reports as SQL Servre 2008, when returning a date column from a DB2 linked server, the query returns a datetime (with 00:00:00.000 for the time). Running the query in SQL Server 2012, the value is returned as a Date data type, which is what it should be returned as in a SQL Server 2008/R2 database.
I'm at a loss where to look next. The date/datetime problem was the original trigger for looking at this, but it has become a much larger puzzle. Obviously, I could simply CAST the incoming data as a date value (which does work, oddly enough), but this doesn't explain the RESTORE returning a DB to compatibility level 90, or the DB2 query returning a datetime instead of a date from the linked server. Any suggestions would be gladly accepted.
sql-server sql-server-2008 sql-server-2012 version restore edited Nov 12 '18 at 19:58
marc_s
571k12811031252
asked Nov 12 '18 at 19:51
Laughing VergilLaughing Vergil
2,2901919
add a comment |
We have four different databases on a SQL Server 2008 (not SP2 - don't ask me why) database server that have an odd problem.
When listed from sys.databases, these databases report that their compatibility is SQL Server 2008/R2 (compatibility level 100) as shown for these three of the DBs here:
MyDB_Name 100 SQL Server 2008/R2
MyDB_Name_MirrorTables 100 SQL Server 2008/R2
MyDB_Name_Reporting 100 SQL Server 2008/R2
As output from this query:
select
name, compatibility_level , version_name =
case compatibility_level
when 90 then 'SQL Server 2005'
when 100 then 'SQL Server 2008/R2'
when 110 then 'SQL Server 2012'
else 'unknown - ' + convert(varchar(10), compatibility_level)
end
from sys.databases
Restores from backups of other user DBs on the server restore correctly as compatibility level 100. However, restores of these four DBs from backups restore as compatibility level 90 (SQL Server 2005). Only these four DBs appear to have this issue.
To test this, I took a manual backup of one of these DBs with only the options INIT, SKIP specified. I then restored this backup to a SQL Server 2012 server. When restored, the DB went through the upgrade process from Version 655 to 706. Nothing else unusual happened.
However, when testing the compatibility level on SQL Server 2012, using the same code as above, this is the value returned:
bwh_MyDB_Name_MirrorTables 90 SQL Server 2005
Additionally, when restored to the original SQL Server 2008 DB server under a different name, the database still returns SQL Server 2005 (compatibility level 90) for the DB version.
Finally, although the DB reports as SQL Servre 2008, when returning a date column from a DB2 linked server, the query returns a datetime (with 00:00:00.000 for the time). Running the query in SQL Server 2012, the value is returned as a Date data type, which is what it should be returned as in a SQL Server 2008/R2 database.
I'm at a loss where to look next. The date/datetime problem was the original trigger for looking at this, but it has become a much larger puzzle. Obviously, I could simply CAST the incoming data as a date value (which does work, oddly enough), but this doesn't explain the RESTORE returning a DB to compatibility level 90, or the DB2 query returning a datetime instead of a date from the linked server. Any suggestions would be gladly accepted.
sql-server sql-server-2008 sql-server-2012 version restore edited Nov 12 '18 at 19:58
marc_s
571k12811031252
asked Nov 12 '18 at 19:51
Laughing VergilLaughing Vergil
2,2901919
Note: As a test, I added a table with Date and Time fields to the DB, then backed it up & restored it again. The table was fine, but the compatibility still shows as 90.
– Laughing Vergil
Nov 13 '18 at 0:01
I can't help with your problem, but just wanted to note that this sounds more like a DBA issue than a programming one, so if you don't get a good answer here, you may want to ask on the specialist DBA stack exchange site - dba.stackexchange.com
– DancingFool
Nov 13 '18 at 5:51
add a comment |
We have four different databases on a SQL Server 2008 (not SP2 - don't ask me why) database server that have an odd problem.
When listed from sys.databases, these databases report that their compatibility is SQL Server 2008/R2 (compatibility level 100) as shown for these three of the DBs here:
MyDB_Name 100 SQL Server 2008/R2
MyDB_Name_MirrorTables 100 SQL Server 2008/R2
MyDB_Name_Reporting 100 SQL Server 2008/R2
As output from this query:
select
name, compatibility_level , version_name =
case compatibility_level
when 90 then 'SQL Server 2005'
when 100 then 'SQL Server 2008/R2'
when 110 then 'SQL Server 2012'
else 'unknown - ' + convert(varchar(10), compatibility_level)
end
from sys.databases
Restores from backups of other user DBs on the server restore correctly as compatibility level 100. However, restores of these four DBs from backups restore as compatibility level 90 (SQL Server 2005). Only these four DBs appear to have this issue.
To test this, I took a manual backup of one of these DBs with only the options INIT, SKIP specified. I then restored this backup to a SQL Server 2012 server. When restored, the DB went through the upgrade process from Version 655 to 706. Nothing else unusual happened.
However, when testing the compatibility level on SQL Server 2012, using the same code as above, this is the value returned:
bwh_MyDB_Name_MirrorTables 90 SQL Server 2005
Additionally, when restored to the original SQL Server 2008 DB server under a different name, the database still returns SQL Server 2005 (compatibility level 90) for the DB version.
Finally, although the DB reports as SQL Servre 2008, when returning a date column from a DB2 linked server, the query returns a datetime (with 00:00:00.000 for the time). Running the query in SQL Server 2012, the value is returned as a Date data type, which is what it should be returned as in a SQL Server 2008/R2 database.
I'm at a loss where to look next. The date/datetime problem was the original trigger for looking at this, but it has become a much larger puzzle. Obviously, I could simply CAST the incoming data as a date value (which does work, oddly enough), but this doesn't explain the RESTORE returning a DB to compatibility level 90, or the DB2 query returning a datetime instead of a date from the linked server. Any suggestions would be gladly accepted.
sql-server sql-server-2008 sql-server-2012 version restore edited Nov 12 '18 at 19:58
marc_s
571k12811031252
asked Nov 12 '18 at 19:51
Laughing VergilLaughing Vergil
2,2901919
We have four different databases on a SQL Server 2008 (not SP2 - don't ask me why) database server that have an odd problem.
When listed from sys.databases, these databases report that their compatibility is SQL Server 2008/R2 (compatibility level 100) as shown for these three of the DBs here:
MyDB_Name 100 SQL Server 2008/R2
MyDB_Name_MirrorTables 100 SQL Server 2008/R2
MyDB_Name_Reporting 100 SQL Server 2008/R2
As output from this query:
select
name, compatibility_level , version_name =
case compatibility_level
when 90 then 'SQL Server 2005'
when 100 then 'SQL Server 2008/R2'
when 110 then 'SQL Server 2012'
else 'unknown - ' + convert(varchar(10), compatibility_level)
end
from sys.databases
Restores from backups of other user DBs on the server restore correctly as compatibility level 100. However, restores of these four DBs from backups restore as compatibility level 90 (SQL Server 2005). Only these four DBs appear to have this issue.
To test this, I took a manual backup of one of these DBs with only the options INIT, SKIP specified. I then restored this backup to a SQL Server 2012 server. When restored, the DB went through the upgrade process from Version 655 to 706. Nothing else unusual happened.
However, when testing the compatibility level on SQL Server 2012, using the same code as above, this is the value returned:
bwh_MyDB_Name_MirrorTables 90 SQL Server 2005
Additionally, when restored to the original SQL Server 2008 DB server under a different name, the database still returns SQL Server 2005 (compatibility level 90) for the DB version.
Finally, although the DB reports as SQL Servre 2008, when returning a date column from a DB2 linked server, the query returns a datetime (with 00:00:00.000 for the time). Running the query in SQL Server 2012, the value is returned as a Date data type, which is what it should be returned as in a SQL Server 2008/R2 database.
I'm at a loss where to look next. The date/datetime problem was the original trigger for looking at this, but it has become a much larger puzzle. Obviously, I could simply CAST the incoming data as a date value (which does work, oddly enough), but this doesn't explain the RESTORE returning a DB to compatibility level 90, or the DB2 query returning a datetime instead of a date from the linked server. Any suggestions would be gladly accepted.
sql-server sql-server-2008 sql-server-2012 version restore sql-server sql-server-2008 sql-server-2012 version restore edited Nov 12 '18 at 19:58
marc_s
571k12811031252
asked Nov 12 '18 at 19:51
Laughing VergilLaughing Vergil
2,2901919
edited Nov 12 '18 at 19:58
marc_s
571k12811031252
asked Nov 12 '18 at 19:51
Laughing VergilLaughing Vergil
2,2901919
edited Nov 12 '18 at 19:58
marc_s
571k12811031252
edited Nov 12 '18 at 19:58
marc_s
571k12811031252
edited Nov 12 '18 at 19:58
marc_s
571k12811031252
571k12811031252
asked Nov 12 '18 at 19:51
Laughing VergilLaughing Vergil
2,2901919
asked Nov 12 '18 at 19:51
Laughing VergilLaughing Vergil
2,2901919
asked Nov 12 '18 at 19:51
Laughing VergilLaughing Vergil
2,2901919
2,2901919
Note: As a test, I added a table with Date and Time fields to the DB, then backed it up & restored it again. The table was fine, but the compatibility still shows as 90.
– Laughing Vergil
Nov 13 '18 at 0:01
I can't help with your problem, but just wanted to note that this sounds more like a DBA issue than a programming one, so if you don't get a good answer here, you may want to ask on the specialist DBA stack exchange site - dba.stackexchange.com
– DancingFool
Nov 13 '18 at 5:51
add a comment |
Note: As a test, I added a table with Date and Time fields to the DB, then backed it up & restored it again. The table was fine, but the compatibility still shows as 90.
– Laughing Vergil
Nov 13 '18 at 0:01
I can't help with your problem, but just wanted to note that this sounds more like a DBA issue than a programming one, so if you don't get a good answer here, you may want to ask on the specialist DBA stack exchange site - dba.stackexchange.com
– DancingFool
Nov 13 '18 at 5:51
Note: As a test, I added a table with Date and Time fields to the DB, then backed it up & restored it again. The table was fine, but the compatibility still shows as 90.
– Laughing Vergil
Nov 13 '18 at 0:01
Note: As a test, I added a table with Date and Time fields to the DB, then backed it up & restored it again. The table was fine, but the compatibility still shows as 90.
– Laughing Vergil
Nov 13 '18 at 0:01
I can't help with your problem, but just wanted to note that this sounds more like a DBA issue than a programming one, so if you don't get a good answer here, you may want to ask on the specialist DBA stack exchange site - dba.stackexchange.com
– DancingFool
Nov 13 '18 at 5:51
I can't help with your problem, but just wanted to note that this sounds more like a DBA issue than a programming one, so if you don't get a good answer here, you may want to ask on the specialist DBA stack exchange site - dba.stackexchange.com
– DancingFool
Nov 13 '18 at 5:51
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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%2fstackoverflow.com%2fquestions%2f53269155%2frestoring-a-backup-of-a-sql-server-2008-compatibility-100-db-creates-a-sql-ser%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- 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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2fstackoverflow.com%2fquestions%2f53269155%2frestoring-a-backup-of-a-sql-server-2008-compatibility-100-db-creates-a-sql-ser%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
Note: As a test, I added a table with Date and Time fields to the DB, then backed it up & restored it again. The table was fine, but the compatibility still shows as 90.
– Laughing Vergil
Nov 13 '18 at 0:01
I can't help with your problem, but just wanted to note that this sounds more like a DBA issue than a programming one, so if you don't get a good answer here, you may want to ask on the specialist DBA stack exchange site - dba.stackexchange.com
– DancingFool
Nov 13 '18 at 5:51