I'm kind of new here, so I'm not sure where to put this. This is how to un-screw webmatrix's migration tool when migrating from SQL CE to Express (worked for me in version 6). Keep in mind, this is from a umbraco / asp.net noob, so there is probably a better way to do it. This is just the only way I could figure it out without having to spend hours learning about sql connections.
I used webmatrix 2's migration tool to migrate from SQL CE to Express, and forgot to back it up. For me, the website wouldn't load after that. So if you have done something similar, this is how you can fix it.
You can immediately restore functionality back to your website by going to the /App_Data/ directory and renaming your databse file (mine was Umbraco.sdf.backup) so it has the correct file extension.
After that, download the Export Sql Compact Edition package (it works for Umbraco v6). Follow the instructions to get a .sql file that will migrate the database schema and data.
Once you have the script, go back to webmatrix and use the app gallery to install another instance of Umbraco. When it asks for the database you want to use, make sure you choose SQL and not SQL CE. It will give you the credentials for the database - make sure to copy and paste that somewhere.
Once the site is up in webmatrix, go to the web.config file and look at the line for the database connection information. It should something like:
"<addname="umbracoDbDSN" . . .>"
Copy and paste that line where you put your database credentials somewhere. Now close the temporary umbraco instance you made, and go back in webmatrix to your old instance of Umbraco.
Find the same line(s) in the web.config file that are already there, and comment them out. Then copy and paste the database connection info in the same area and save.
Finally, make sure you have management studio installed. Open mgmt studio and connect to your sql server. Now find the umbraco database that was created from the other website and click on it. Now navigate to where the Export Sql CE package saved the script. Open that up with management studio, and then execute the script.
You should now successfully have the original umbraco instance connected, and it should work. If you plan on deploying with webmatrix, you should go back and delete any of the connections it lists under databases. Just add a new database with the credentials that you originally copy & pasted when creating a temp umbraco instance.
Thanks for sharing your experience and how you handled it - #h5yr! :)
I'm wondering what error message you initially got when you did the migrate from webmatrix to sql server express? I'm using the migration tool in webmatrix all the time and so far I've not had any trouble migrating from CE SQL to MS SQL Server 2008.
Hope you enjoy Umbraco despite the migration issue.
I deleted the files / cleared as much as I could off the site before deplying, so I can't tell you exactly what the error messages were. You seem like a nice person, but not nice enough for me to attempt to recreate the problem ;)
Anyway, after staring / tinkering with it for an hour, this is what I thought the problems were.
First off, the connection string in the web.config did not update, although I've read that webmatrix is supposed to change that. After getting a correct connstring in there, I started getting errors related to the databas schema and values (duplicate primary keys, missing data, etc.) .
So this may have been a fluke if it works for other people. I figured I'd drop this here just in case anyone ever gets into a similar situation without much knowledge about connecting a website to a database.
Thanks for posting this. Happy to hear that the Export SqlCE package helped you. I've hit the same issue with WebMatrix too - hence the reason for making that package. I could never put my finger on the exact cause of the problem either. (Even though many people have success using WebMatrix for database migrations) :-)
Haha, thanks for the kind words. I'll forgive you for not wanting to recreate it all for my sake. (However I would have done it for you though! ;-) - Naah, just kidding).
But then it's nice that Lee has made an awesome tool for the job! :)
The only time I have run into issues was when I had Contour installed on my CE SQL and wanted to migrate. That was not possible for some reason related to Contour.
However I have only done this with v4 installations so don't know if the changes in the DB schemas on v6 could be the cause...Once I try a v6 I'll try to post in here wether I as succesfull or not.
Thanks for this, I have now been able to restore my local site. I inadvertently migrated the DB. I followed your instructions, copied the Umbraco.sdf.backup and renamed it back to Umbraco.sdf and the local website is now working as before.
Hi, i have developed a project in Umbraco 7 and ofcourse that time i selected SQL CE Database. Now i want to deplot the project Live and my hosting plan accepts MS SQL Database. After reading many articles i was able to Create umbraco.sdf to umbraco.sql. I just copied the script from umbraco.sql and created a new local database called umbraco which now has all the data from my original umbraco.sdf.
What should be the next step? Do i need to do changes in web.config? Do i need to delete .sdf file? Any assistance on this would be appreciated
Yes you need to update your web.config and point it to your newly created MS SQL server database instead of the old CE. Hold off removing your CE until you´ve done this just to be sure.
Hi Dennis appreciate you prompt help. Feels better now. However i need help in understanding the last step from the tutorial you just stated above.
it says " key=”umbracoDbDSN” with "
I'd like to know in
1. which row should i copy the above code with my db credentials.
2. Which row should i delete?
3. How will it recogninse that my db is now sql server?
Attaching an image of my webconfig , would be immensely thankful for help
Once installed you will be able to connect to your .sdf database and script it to a .sql file. Then use any sql client or cli to import the script file to a SQL server database.
Once you database is migrated, change your connection string to fit, and the sit back and enjoy your doings :)
Migrating from SQL CE to Express
I'm kind of new here, so I'm not sure where to put this. This is how to un-screw webmatrix's migration tool when migrating from SQL CE to Express (worked for me in version 6). Keep in mind, this is from a umbraco / asp.net noob, so there is probably a better way to do it. This is just the only way I could figure it out without having to spend hours learning about sql connections.
I used webmatrix 2's migration tool to migrate from SQL CE to Express, and forgot to back it up. For me, the website wouldn't load after that. So if you have done something similar, this is how you can fix it.
You can immediately restore functionality back to your website by going to the /App_Data/ directory and renaming your databse file (mine was Umbraco.sdf.backup) so it has the correct file extension.
After that, download the Export Sql Compact Edition package (it works for Umbraco v6). Follow the instructions to get a .sql file that will migrate the database schema and data.
Once you have the script, go back to webmatrix and use the app gallery to install another instance of Umbraco. When it asks for the database you want to use, make sure you choose SQL and not SQL CE. It will give you the credentials for the database - make sure to copy and paste that somewhere.
Once the site is up in webmatrix, go to the web.config file and look at the line for the database connection information. It should something like:
Copy and paste that line where you put your database credentials somewhere. Now close the temporary umbraco instance you made, and go back in webmatrix to your old instance of Umbraco.
Find the same line(s) in the web.config file that are already there, and comment them out. Then copy and paste the database connection info in the same area and save.
Finally, make sure you have management studio installed. Open mgmt studio and connect to your sql server. Now find the umbraco database that was created from the other website and click on it. Now navigate to where the Export Sql CE package saved the script. Open that up with management studio, and then execute the script.
You should now successfully have the original umbraco instance connected, and it should work. If you plan on deploying with webmatrix, you should go back and delete any of the connections it lists under databases. Just add a new database with the credentials that you originally copy & pasted when creating a temp umbraco instance.
That was longer than I thought it would be :\ .
Hi Curetos and welcome to our :)
Thanks for sharing your experience and how you handled it - #h5yr! :)
I'm wondering what error message you initially got when you did the migrate from webmatrix to sql server express? I'm using the migration tool in webmatrix all the time and so far I've not had any trouble migrating from CE SQL to MS SQL Server 2008.
Hope you enjoy Umbraco despite the migration issue.
Looking forward to hearing more from you.
Cheers,
Jan
I deleted the files / cleared as much as I could off the site before deplying, so I can't tell you exactly what the error messages were. You seem like a nice person, but not nice enough for me to attempt to recreate the problem ;)
Anyway, after staring / tinkering with it for an hour, this is what I thought the problems were.
First off, the connection string in the web.config did not update, although I've read that webmatrix is supposed to change that. After getting a correct connstring in there, I started getting errors related to the databas schema and values (duplicate primary keys, missing data, etc.) .
So this may have been a fluke if it works for other people. I figured I'd drop this here just in case anyone ever gets into a similar situation without much knowledge about connecting a website to a database.
HI Curetos,
Thanks for posting this. Happy to hear that the Export SqlCE package helped you. I've hit the same issue with WebMatrix too - hence the reason for making that package. I could never put my finger on the exact cause of the problem either. (Even though many people have success using WebMatrix for database migrations) :-)
Cheers, Lee.
Hi Curetos
Haha, thanks for the kind words. I'll forgive you for not wanting to recreate it all for my sake. (However I would have done it for you though! ;-) - Naah, just kidding).
But then it's nice that Lee has made an awesome tool for the job! :)
The only time I have run into issues was when I had Contour installed on my CE SQL and wanted to migrate. That was not possible for some reason related to Contour.
However I have only done this with v4 installations so don't know if the changes in the DB schemas on v6 could be the cause...Once I try a v6 I'll try to post in here wether I as succesfull or not.
Happy easter guys!
/Jan
Hi Curetos,
Thanks for this, I have now been able to restore my local site. I inadvertently migrated the DB. I followed your instructions, copied the Umbraco.sdf.backup and renamed it back to Umbraco.sdf and the local website is now working as before.
Thanks again!
Hi, i have developed a project in Umbraco 7 and ofcourse that time i selected SQL CE Database. Now i want to deplot the project Live and my hosting plan accepts MS SQL Database. After reading many articles i was able to Create umbraco.sdf to umbraco.sql. I just copied the script from umbraco.sql and created a new local database called umbraco which now has all the data from my original umbraco.sdf. What should be the next step? Do i need to do changes in web.config? Do i need to delete .sdf file? Any assistance on this would be appreciated
Hi Nitin.
Yes you need to update your web.config and point it to your newly created MS SQL server database instead of the old CE. Hold off removing your CE until you´ve done this just to be sure.
Check this tutorial, in the last step and you´ll understand: http://www.geektantra.com/2012/10/umbraco-deployment-exporting-ms-sql-ce-database-to-ms-sql-server-express/
As soon as you´ve done this and you make sure everything works, you´ll have no need of the old CE database.
Best of luck to you!
Hi Dennis appreciate you prompt help. Feels better now. However i need help in understanding the last step from the tutorial you just stated above.
it says " key=”umbracoDbDSN” with "
I'd like to know in 1. which row should i copy the above code with my db credentials. 2. Which row should i delete? 3. How will it recogninse that my db is now sql server?
Attaching an image of my webconfig , would be immensely thankful for help
For Visual Studio, users I recommend this add-in: https://visualstudiogallery.msdn.microsoft.com/0e313dfd-be80-4afb-b5e9-6e74d369f7a1
Once installed you will be able to connect to your .sdf database and script it to a .sql file. Then use any sql client or cli to import the script file to a SQL server database.
Once you database is migrated, change your connection string to fit, and the sit back and enjoy your doings :)
thanks casper, can you mention updated syntax for writing connection string in web.config file. would be of great help
sdf connectionstring:
sql server connectionstring:
great! exactly what i was looking for. thanks
is working on a reply...