SQL Upgrades are a one way street.

Published On: 2009-10-12By:

That’s right folks.  Once you upgrade SQL Server versions there is no going back.

This means that SQL 2008 R2 databases can not be detached from a SQL 2008 R2 instance and attached to a SQL 2008 instance.  You can’t backup the SQL 2008 R2 database and restore is to a SQL 2008 instance.

You can’t take a SQL 2008 database and move it to a SQL 2005 instance.  Same goes from SQL 2005 to SQL 2000, or from SQL 2000 to SQL 7. No, you can’t put the SQL 2008 database into the SQL 2005 compatibility mode (aka 100) and then do it.  It still won’t work.

You can move up from SQL 2000, to SQL 2005.  Or from SQL 2005 to SQL 2008.  If you are real brave you can move from SQL 2000 directly to SQL 2008.  But these are one way operations.

When SQL Server moved from one version to another the way that the headers in the database file are written are changed.  Especially from SQL 2000 to SQL 2005, there were a lot of changes (nothing like the changes from SQL 6.5 to SQL 7).  There were less from SQL 2005 to SQL 2008, and less again from SQL 2008 to SQL 2008 R2 (so far).

If you need to go back for some reason, your only option is to script out all the objects, and create them on the older version.  You can then use BCP, DTS, SSIS, or scripting out the data to move the data from the higher version instance to the lower version instance.

Denny


Contact the Author | Contact DCAC

3 responses to “SQL Upgrades are a one way street.”

  1. PhilFactor says:

    This is true, there are several ways of doing a ‘downgrade’ with a third-party tool. ISQL Toolbelt is the one I’m most familiar with. One can use either SQL Compare/Data compare, or SQL Packager will do it, or you can script something to do it with the SDK. It is unusual to want to downgrade, and it is a very slow business on a large database, but it always makes the upgrade a more relaxing experience if you know you can either do a bareboned downdrade or resore an old backup and use Data Compare to update the backup from the ‘upgraded’ system.

  2. Wqwetto says:

    >> nothing like the changes from SQL 6.5 to SQL <<
    What are you talking about? The changes were *dramatic*!

    In 6.5 dbs were assigned not to files but to devices which were taking up whole disk partitions (mostly). MSSQL7 persistence storage was a total rewrite of the sybase cruft.

    cheers,

  3. mrdenny says:

    I was attempting to (and apparently poorly) show that there were a lot of changes from 8-9, but there were even more from 6.5-7.

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner    Microsoft MVP    Microsoft Certified Master    VMWare Partner    VMWare vExpert
   Best Full-Service Cloud Technology Consulting Company    Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link