Migrating an EPiServer database to SQL Server 2005 from SQL Server 2008
This blog post is about migrating an EPiServer database from SQL Server 2008 to SQL Server 2005. We’ll also briefly address an issue with LINQ to Entities when going from SQL Server 2008 to SQL Server 2005.
Why migrate from SQL Server 2008 to SQL Server 2005?
If the production environment hosted by the client or by a third-party hasn’t been defined or analyzed there may be case of having to migrate the solution to an older technology. As in this case migrating an SQL Server 2008 database to SQL Server 2005.
The primary issue is that SQL Server 2005 does not allow restoring backup versions of SQL Server 2008 databases using the old backup/restore pattern.
In order to circumvent this we need to script the EPiServer database in SQL Server 2005 compatibility mode.
Creating the script in SQL Server 2008
The first order of business is to fire up SQL Server Management Studio for SQL Server 2008 where the EPiServer database is hosted. From here we’ll create the script that is compatible with SQL Server 2005. We’re going to script all objects in the database as well as exporting the data.
Start by right clicking the database and select Tasks –> Generate Scripts. Click next to bypass the welcome message and on the Select Database dialog select Script all objects in the seletected database and click next.
In the Choose Script Options dialog we need to set the parameters that will generates a script that creates the database, has the SQL Server 2005 as target version and scripts the data in the database as well. Finally save the script to file.
Restoring to SQL Server 2005
Start SQL Server Management Studio for SQL Server 2005 and open the script file we just created.
Before running the script we need to make sure that the file paths to the database file and log are correct. Do this by manually editing the script generated and change the FILENAME paths.
Run the script and the database is created on SQL Server 2005.
A quick note on LINQ to Entities
If a model using Entity Framework has been created for SQL Server 2008 and the database is migrated to SQL Server 2005 you may receive the error ‘Type datetime2 is not a defined system type’.
This can be resolved by manually editing the EDMX-file and setting the ProviderManifestToken to 2005. This will set the target SQL Server as a 2005 version and resolve the issue after a recompile.