November 11, 2011 at 9:08 am
Hello,
If I change a database on a SQL 2005 server from compatibility level 80 (having once been a SQL 2000 DB) to 90, can I change it back to 80 if need be? Or do I need to restore from a back up of when if was 80 before the change?
Thank you,
D.
November 11, 2011 at 9:23 am
Duran (11/11/2011)
Hello,If I change a database on a SQL 2005 server from compatibility level 80 (having once been a SQL 2000 DB) to 90, can I change it back to 80 if need be? Or do I need to restore from a back up of when if was 80 before the change?
Thank you,
D.
If you don't touch the database, you can.
If you add 2005-specific features (such as indexed views), you cannot.
November 11, 2011 at 9:25 am
As Revenant said, just don't add features that are new in 2005 and you will be fine. I have had to do this on a few occasions and it works well.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 11, 2011 at 9:34 am
Thanks for replying Guys, the only thing I changed was the page verify from Torn, to checksum, but changed it back to torn again. Does that make a difference? I do not think it does.
Regards,
D.
November 11, 2011 at 9:42 am
Change it back to checksum.
Some 2005 features are fine, some are not. The ones that are not purely have to do with the query processor. It's only the query processor (and parser, QO, algebriser) that care about the compat level and it's just in the sense of what commands are valid and what are not.
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
November 11, 2011 at 9:46 am
Revenant (11/11/2011)
Duran (11/11/2011)
Hello,If I change a database on a SQL 2005 server from compatibility level 80 (having once been a SQL 2000 DB) to 90, can I change it back to 80 if need be? Or do I need to restore from a back up of when if was 80 before the change?
Thank you,
D.
If you don't touch the database, you can.
If you add 2005-specific features (such as indexed views), you cannot.
Err weren't indexed view available in 2000? Never really used sql 7, but I'm pretty sure about 2000!
Hey Google still works.
IV were introduced in sql 2000 => http://technet.microsoft.com/en-us/library/cc917717.aspx
November 11, 2011 at 9:47 am
Nice, thanks Gail. Have good weekends everyone!
Kind Regards,
D.
November 11, 2011 at 9:50 am
To give you an idea about how the compat level works, I'm busy compressing indexes (a SQL 2008 feature) in a database set to compat mode 90 (sql 2005). Working fine.
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
November 11, 2011 at 9:52 am
Revenant (11/11/2011)
If you add 2005-specific features (such as indexed views), you cannot.
You can always change the compatability level. There are no checks for doing so. Some stuff (that uses newer query constructs) may break afterwards when they run, but they won't prevent the change of compat level.
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
November 11, 2011 at 9:53 am
GilaMonster (11/11/2011)
Revenant (11/11/2011)
If you add 2005-specific features (such as indexed views), you cannot.You can always change the compatability level. There are no checks for doing so. Some stuff (that uses newer query constructs) may break afterwards when they run, but they won't prevent the change of compat level.
Can you imagine the amount of work to validate that? :hehe: Even assuming you have nothing in the front end that builds query!
November 11, 2011 at 10:05 am
Ninja's_RGR'us (11/11/2011)
Revenant (11/11/2011)
Duran (11/11/2011)
Hello,If I change a database on a SQL 2005 server from compatibility level 80 (having once been a SQL 2000 DB) to 90, can I change it back to 80 if need be? Or do I need to restore from a back up of when if was 80 before the change?
Thank you,
D.
If you don't touch the database, you can.
If you add 2005-specific features (such as indexed views), you cannot.
Err weren't indexed view available in 2000? Never really used sql 7, but I'm pretty sure about 2000!
Hey Google still works.
IV were introduced in sql 2000 => http://technet.microsoft.com/en-us/library/cc917717.aspx
I should have been more specific: yes, indexed views were introduced in 2000 but they were vastly improved in 2005 and the new features of 2005 are not backward compatible. So they are something to watch for.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply