More in this section

Forums / Developing with Sitefinity / Migrate data from SQL standalone to older SQL Express version?

Migrate data from SQL standalone to older SQL Express version?

4 posts, 0 answered
  1. Steve
    Steve avatar
    13 posts
    Registered:
    29 Apr 2011
    05 Jul 2012
    Link to this post
    I am about to deploy a project for a client, only to find out my SQL data requires migration.

    I initially designed the site using SQL Express 2008 R2 as a standalone database on my local box. I avoided using an attached database located under App_Data folder.

    Now that we are ready to deploy, the host providers mainly use SQL Server 2005 Express. I've been able to attach to an MDF file in my App_Data folder using my SQL Express 2008 R2  instance.

    I don't know how to downgrade the database to SQL Express 2005. I know I can't restore a 2008 R2 backup an a 2005 instance. I can always script the schema. But I don't know how to transfer the data. Script the data? Or use SSIS to copy the data?

    What is the best way to do this?

    Steve
  2. MB
    MB avatar
    302 posts
    Registered:
    09 Jan 2005
    06 Jul 2012
    Link to this post
    Obviously, back-up before trying this...

    I seem to recall that you can set the compatibility mode of the database allow it to be opened in older versions. i.e. Open it with SQL-Server-2008 and set the compatibility (one of the Option settings on the database) to the lower version. I believe this should allow SQL-Server-2005 to open and use it.

    I'm not 100% sure, so as I said... make a copy of the database first.
  3. Victor Velev
    Victor Velev avatar
    1345 posts
    Registered:
    06 Jul 2015
    06 Jul 2012
    Link to this post
    Hello Steve,

    Basically downgrading is not supported by the SQL server software, but still you can try few workarounds ( having in mind Mike's suggestion ). What I would suggest is to use a data compare tool. For example the one that comes with Visual Studio.

    You can a Sitefinity database on your 2005 server and then use data compare to push the contents from the running 2008 instance to the 2005 server. Still you may encounter some version mismatch errors on the way, but you can try also 3rd party tools to migrate your data.

    Kind regards,
    Victor Velev
    the Telerik team
    Do you want to have your say in the Sitefinity development roadmap? Do you want to know when a feature you requested is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
  4. SelAromDotNet
    SelAromDotNet avatar
    912 posts
    Registered:
    18 Jul 2012
    09 Jul 2012
    Link to this post
    Steve,

    I've had to deal with this a few times in the past, and have found that the best way to do it is indeed to script the database and data.

    Unfortunately this usually means a huge script that can't be run in SQL Manager because it runs out of memory!

    I have blogged about this however, that you can use the command line to run the script, so take a look at this and see if it works for you: Downgrading an SQL Server Database Using Scripts

    Hope this is helpful!
4 posts, 0 answered