How to migrate from sql server express to sql server?

Question

I have been developing a web app that I wish to deploy on godaddy that is using a .mdf file for data. I would like to use a sql server database that comes with the account for my data. What is involved in setting up my config and how do I get the schema copied over?

Here is the error I get:

The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version.

Answer 1

is not it a simple backup/restore process?

EDIT: following comments, the best way would be effectively to generate the database script and launch it on the server. I have been doing that kind of job with EMS Sql Manager (May God Bless This One!) where you can generate a full DDL/Data script and launch it to create the same database on another server. Great Tool

Answer 2

It's even easier than backup/restore. You just upload the file and attach your server instance to it. The trick is in how exactly you are allowed to do that, because just about every hosting provider has their own set of tools. Can you connect directly to your server instance via management studio?

Answer 3

Check out xSQL Bundle Lite Edition from xSQL Software

It can generate the script for you to run on your server on GoDaddy.

Does GoDaddy give you access to connect to your SQL database via the management tools with SQL Express?

Answer 4

If all you want is the schema set up on GoDaddy, you can follow the steps here: http://help.godaddy.com/topic/102/article/3324

However, if you want to move your data and the schema across to GoDaddy, you should probably use someting like Microsft's Database Publishing Wizard - Scott Guthrie has a good post on this here: http://weblogs.asp.net/scottgu/archive/2006/12/22/recipe-deploying-a-sql-database-to-a-remote-hosting-environment-part-1.aspx And GoDaddy have a support document about using it here: http://help.godaddy.com/topic/102/article/3439

Finally, you'll need to update the Connection Strings in your web.config - either update the LocalServer connection string to point to your GoDaddy SQL instance (the connection string details are available from your admin interface), or create a new ConnectionString, and update the Connection details in each of the providers that you have set up (Profile, Membership, Roles, etc).

Answer 5

SQL hosting toolkit's "Database Publishing Wizard" http://www.codeplex.com/sqlhost helped to generate the right sql script.