August 16, 2011 at 7:01 am
I just recently had a project where a process that was being used to convert a SQL 2000 database into a 2008 R2 DBMS was abandoned, in favor of simply doing a backup and restore of the database into the 2008 R2 environment. Compatibility level 100 was enabled onto the database after the restore, and now from what I am told - it's a full 2008 R2 database.
My concern here is that what I actually have still is a SQL 2000 database with compatibility level 100 enabled on it (meaning if there are an specific features that will work in the 2008 R2 environment, it can take advantage of them).
I'm foggy on the understanding, but want to make sure I have it clear. The original process was written in an effort to make the DBMS into a true 2008 R2 database by copying the data into temp tables, and then back into real tables in the 2008 R2 environment (because of data type changes that were needed for columns that were using TEXT and IMAGE as their setting). Other objects were simply scripted into their own DDL and then run in the 2008 R2 SSMS against the new database, but this is not the core concern.
Hope this is not confusing to understand, but any insight into my concern here would be appreciated.
August 16, 2011 at 7:07 am
As soon as the database was restored to 2008 R2 it became completely a 2008 R2 database in every way.
The compatibility level just affects how the query processor and parser behave for certain T-SQL constructs and has nothing to do with the version (create or current) of the database
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 16, 2011 at 7:46 am
That's all I needed from one of the authority!
Thank you Gail!
🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply