February 5, 2011 at 10:32 am
On my laptop, I started a database under SQL Server 2005 Express, then upgraded 2005 Express to 2008 R2 Express.
Later, I decided I wanted to move the database to a 2008 STD on my server.
Research told me that moving a 2008 R2 (10.5) database to a 2008 (10.0) installation would not work. But what if the database was first created at the 2005-level (9.x)? The advice was to check the "Compatibility Level".
Didn't matter. The compatibility_level in the sys.databases column says 90, but the gaining server complained when I attempted to attach it: database is at 661, this server accepts 655 or lower.
So... Compatibility Level is not the same as the Version Level?
February 5, 2011 at 1:24 pm
No.
Compat level determines db behaviour (cfr emulation if not the dblevel of the current engine)
Db version can be seen as the db catalog edition. That's different by sqlserver version. (R2 has indeed another version level)
If you want to move your db to a lower version of sqlserver, you'll have to script it all and pull over the data (e.g.using SSIS).
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 5, 2011 at 2:55 pm
"cfr emulation" - I don't understand this term.
"db's 'catalog' edition" = same.
So, if I had planned this a little better, I could have detached the database, upgraded the DBMS (SQL SERVER 2005 Express to 2008 R2 Express), then attached the database and I would have been fine?
That is to say, had I done the above, I could have then detach/attach to the 2008 STD?
February 5, 2011 at 4:47 pm
Let me translate, compatibility level will affect query behavior in the sense that query optimizer will be limited to the "compatibility level" you have set, it will not use any feature that was introduced on upper versions.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 6, 2011 at 1:07 am
SQL2008R2 <> SQL2008 !!
When you attach a db or restore a db, sqlserver checks the systems information level (=system catalog level) and performs the needed updates.
That's the reason you cannot attach to a lower level.
There are nog "downgrade" scripts.
You could install a SQL2008R2 std edtn.
Then you can attach/restore your db from SQL2008R2 Exp.Edtn
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 7, 2011 at 1:41 am
bhsmither (2/5/2011)
So, if I had planned this a little better, I could have detached the database, upgraded the DBMS (SQL SERVER 2005 Express to 2008 R2 Express), then attached the database and I would have been fine?
No, you wouldn't. As soon as you attach the database to the 2008 R2 instance it is upgraded to that version and could not later be reverted to an older version without scripting out the structure and data and re-importing.
February 7, 2011 at 6:01 am
PaulB-TheOneAndOnly (2/5/2011)
Let me translate, compatibility level will affect query behavior in the sense that query optimizer will be limited to the "compatibility level" you have set, it will not use any feature that was introduced on upper versions.
Just to clarify that it's a compatibility mode, not an emulation mode so it's not strictly true that more recent features than the mode you've set compatibility to will be disabled.
It depends on whether the new feature can be enabled without having any backwards compatibility issues with features that are now deprecated.
For example, CTE's work fine in SQL 2000 compatibility mode.
The main point of the modes are to enable deprecated/changed features to still work according to how they worked in a previous version, not to emulate the previous version entirely.
February 7, 2011 at 6:16 am
some more info regarding compatibility mode can be found at http://technet.microsoft.com/en-us/library/bb510680.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 7, 2011 at 7:11 am
Thank you all for the conversation.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply