“You can’t go back. You have to go forward to go back.”
– Gene Wilder in Willy Wonka & the Chocolate Factory
This month’s T-SQL Tuesday is hosted by Sankar Reddy ( blog | Twitter ) and the topic is Misconceptions in SQL Server. One misconception I frequently see in the customer base with which I work is the idea that version is not that important. Most often, I see this in the case of ODBC drivers used by an application I support. It is not uncommon for me to find a customer running SQL Server 2005 or 2008 and using the SQL 2000 drivers. Does it work? It “appears” to…but there are changes to the ODBC drivers for each release, and they are not documented anywhere that I can find. I have seen issues with SQL 2000 drivers against SQL 2005, and also with the first release of the 2005 drivers. In all cases, I always recommend using the ODBC driver that is compatible with the server version you are running, and always use the latest driver available.
I then started thinking about how often I have seen forum posts from individuals who upgrade a database to a new version (e.g. SQL 2005 to SQL 2008), then want to downgrade. You cannot easily go back, unless you have a backup from before your upgrade (which you do have, right?), then you just restore the backup on the original version and you’re good to go.
Why can’t you downgrade? Every database stores the SQL Server version to which it was last attached, and this is located on the boot page of the database. Paul Randal ( blog | Twitter ) discussed it in more detail in a 2008 post, but this is the version you see when you run
{code}SELECT @@version{/code}
within SSMS or Query Analyzer.
Therefore, you cannot go from 2005 back to any prior version, from 2008 R1 to any prior version or from 2008 R2 to any prior version. Yes, you read that right. When you upgrade to SQL Server 2008 R2, that is a version upgrade (Paul talks about that here).
What if you have upgraded, and have no backup, and you need to downgrade? Well, you will find many posts with the same question, and the quick answer is that you have to create a new database in the original version and move all the data via scripts, SSIS, bcp, etc. Not fun.
And since I was researching versions, it made sense to mention Editions. You can always go up: Express to Standard, Standard to Enterprise. What about going back? You cannot take a Standard or Enterprise database and restore it on an Express installation. You can take an Enterprise database and restore it on Standard Edition if and only if you are not using any of the Enterprise features. Again, Paul has a post that details the features that matter if you’re thinking about moving from Enterprise back to Standard Edition.
In summary:
- use the latest ODBC drivers that match the server version you’re running
- going up in version or edition is supported
- you cannot downgrade version natively (it requires manual effort)
- you can only downgrade Edition if you are going from Enterprise to Standard and not using any of the Enterprise features
- test, test and test before you upgrade
Good post and some good issues flagged. I suppose you could argue that when moving from editions, the use of “Enterprise” features should be just disabled rather than prevent a restore. I’m not so convinced that the difficulties faced due to the restriction on downgrade from versions is so problematic. Don’t think doing db upgrade/ downgrades should be taken lightly in the first place …but then I am assuming an element of planning and specialist consultation has occurred 🙂