Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Jason Espin 368 posts 1335 karma points
    Jun 19, 2014 @ 16:47
    Jason Espin
    1

    Convert Umbraco SQL CE database to SQL Express

    Hi all,

    I began developing my Umbraco site using SQL CE however I now wish to convert the SQL CE database to a SQL Express database to further future proof the solution and make it ready for deployment.

    I understand that is is possible to use WebMatrix to convert an SDF file into a SQL Express database however, when I do this, I am unable to find where it saves the new SQL express database to. It also creates a new set of records in the Web Config file meaning there are two sets of records (connection strings). One for the original SQL CE database and one for the new SQL Express database.

    I have tried deleting the SQL CE record but I then get an object not found message suggesting that the new SQL Express database may be in the wrong place or the connection string (retrieved from WebMatrix) may be incorrect.

    Does anyone know of an official and easy to following guide to converting a Umbraco SQL CE database to a SQL Express database with additional information relating to how to wire everything up so that the site functions as it did under SQL CE?

    I am using the latest version of Umbraco, WebMatrix 3 and use Visual Studio 2013 for development and file editing.

    Any help would be greatly appreciated.

  • Dan 1288 posts 3921 karma points c-trib
    Jun 19, 2014 @ 17:04
    Dan
    4

    Hi Jason,

    I've always found SQLCeToolbox to work better than any in-built tools for converting Ce to Express. It just seems to work and doesn't have any issues with large dbs as Webmatrix does (or did when I last used it). I'd normally set up a SQL Express database, script the dev Ce database out using SQLCeToolbox, then run the script in your Express database to set up the structure/data in there, then manually just change your connection string in web.config from something like:

    <add name="umbracoDbDSN" connectionString="Data Source=|DataDirectory|\Umbraco.sdf;Flush Interval=1;" providerName="System.Data.SqlServerCe.4.0" />
    

    ...to something like:

    <add name="umbracoDbDSN" connectionString="server=.\SQLExpress;database=dbname;user id=dbuser;password=dbpassword" providerName="System.Data.SqlClient" />
    

    I don't think there are any other steps to take - it should "just work".

    Hope this helps.

  • Dennis Aaen 4500 posts 18255 karma points admin hq c-trib
    Jun 19, 2014 @ 18:23
    Dennis Aaen
    1

    Hi Jason,

    I would like to add how you can convert Umbraco SQL CE database to SQL Express, using the Webmatrix. Since you already use Webmatix, you an easly convert  your Umbraco SQL CE database to SQL Express database.

    First you need setup a database in  SQL Expreess. Once that you has done that,then open the Webmatrix, and in the lower left corner in the webmatrix UI you can select the database - Then browse to the SQLCE database, it´s located in the \App_Data folder after that a "migrate button" will appear in the upper menu options. By press that button a migrate dialogue appears asking for connection details to the SQL Express database that  you have created.

    Then you done converting your SQL CE database to SQL Express database. Then you have to change the database connection string to the new database like Dan says in his great post.

    Just my inputs, hope these are useful.

    /Dennis

  • Yoni 49 posts 118 karma points
    Jul 29, 2014 @ 08:20
    Yoni
    1

    I would like to add my experience.

    I used the Migrate option in WebMatrix. I did not need to create the DB in advance, Webmatrix did it for me.

    When the process was done, I was left with 2 connections strings. The original one and the new SQL which got the name "Umbraco". I deleted the original one and renamed the new one to "umbracoDbDSN" and everything worked great.

  • Matthew Hardesty 1 post 44 karma points
    Oct 14, 2014 @ 20:02
    Matthew Hardesty
    103

    I'm writing to confirm what Yoni contributed.

    Using the Migrate option in WebMatrix will add a new connection string with the name "Umbraco" to the web.config file. There are two steps, however, that you must take in order for the site to work properly.

    1. Remove or comment out the deprecated connection string ()
    2. Update the name in the newly added connection string from "Umbraco" to "umbracoDbDSN"

    After your edit, the new connection string will be one of these options:

    Using windows authentication

    <add connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=DATABASENAME;Integrated Security=True" name="umbracoDbDSN" providerName="System.Data.SqlClient" />
    

    or not using windows authentication

    <add connectionString="server=.\SQLExpress;database=DATABASENAME;user id=USER;password=PASS" name="umbracoDbDSN" providerName="System.Data.SqlClient" />
    

    I hope this helps.

    -Matt

  • Aleksey Gerasimov 31 posts 95 karma points
    Nov 10, 2015 @ 11:09
    Aleksey Gerasimov
    0

    "Migration" in WebMatrix do not copy data from .sdf file to new database for me.

  • shenyi bao 5 posts 75 karma points
    Dec 28, 2016 @ 23:53
Please Sign in or register to post replies

Write your reply to:

Draft