[Umbraco Deployment] Exporting MS SQL CE Database to MS SQL Server Express

Here is a detailed set of steps to export your MS SQL CE Data file to your SQL Server Instance and deploying your Umbraco development package on to production:

• Install “Microsoft SQL Server Compact 4.0” from http://www.microsoft.com/en-us/download/details.aspx?id=17876
• Install “SQL Server Compact Toolbox (Standalone)” from http://sqlcetoolbox.codeplex.com/releases/view/69910
• Open the downloaded file “SqlCe40Toolbox.exe“.
• Right click on the list of connections and select the option “Add SQL Server Compact 4.0 Connection…“.
• On the dialog box that opens click on “Browse” button and select the file <umbraco_application_directory>/App_Data/umbraco.sdf (<umbraco_application_directory> is extracted form of PublishedWebsite.zip).
• On the same dialog box click “Test Connection” button and if everything is ok clck the “Close” button.
• Now on the “Toolbox” window right click on “Umbraco.sdf” tree item and select “Script Database Schema and Data with BLOBs…” and save the file as “umbraco.sqlce“
• Rename umbraco.sqlce to umbraco.sql.
• Open “SQL Server Management Studio” and connect to your SQL Server/SQL Server Express database.
• Open umbraco.sql on the selected database and click on “Execute“, and the database is ready on SQL Server/SQL Server Express.
• Now make the following change to “web.config” of umbraco to get Umbraco to use the SQL Server database instead of SQL CE Database: Replace the line with key=”umbracoDbDSN” with <add key="umbracoDbDSN" value="Server=<your-server-address>;Database=<your-database-name>;User ID=<your-database-username>;Password=<your-database-password>;Trusted_Connection=False"/>