Migrate data from SQL standalone to older SQL Express versio - Sitefinity - Front- & Back-End Development

All Replies

Posted by Community Admin on 05-Jul-2012 00:00

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

Posted by Community Admin on 06-Jul-2012 00:00

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.

Posted by Community Admin on 06-Jul-2012 00:00

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

Posted by Community Admin on 09-Jul-2012 00:00

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!

This thread is closed