Recently a client asked me for some help moving his website from his own server to host on GoDaddy. The website was created in VB.NET and connected to a Microsoft SQL Server Express 2005 Database. Unfortunately there is no EASY way. There SHOULD be. It should not be this difficult and time consuming to migrate a SQL Server not running on GoDaddy to a GoDaddy hosted SQL Server. I even tried connecting directly to the GoDaddy database but it failed. I don’t think they like remote connections. So here is how I did it.

Needed: SQL Management Studio (the full version, not express). Included with SQL Server 2005 full version. This is needed for generating the scripts to easily create the structure on the new GoDaddy database.

Notes: GoDaddy will not accept a .bak file that was not created by them. I tried…

Step 1: Create SQL Server database on GoDaddy. Try to make this the GoDaddy database the same name as your original database, and the same user name. I do not think it is required, but will make everything a lot easier. Once it’s done you will want to have the GoDaddy SQL Server web admin open.

Step 2: Export and import the structure. Open up SQL Server Studio and find your original database, right click the database -> Tasks… -> Generate Scripts… A wizard will pop up. Select your database, and select all the stuff you want to export, I did everything. Might want to skip the user the source and destination do not match. You will see an option to either save it to a file or new query window. I did a new query window. You can then copy and paste big collection of SQL queries into the GoDaddy Query Analyzer in the database web admin.

Step 3: Export source data from SQL Server Studio to CSV. There are a few ways to do this. Right click the database -> Tasks… -> Export… Select your source database, and on the destination screen select the destination to be a flat file. OR you could also select all the results from a table using a SELECT * FROM table query and then right click on the results window and click Save Results As… This will the results as a CSV in your favorite location. Both methods will only do one table at a time. I could not find a way to export all the data at once, so if you have a lot of tables with a lot of data this will be very time consuming. Maybe higher an student or nephew to do it.

Step 4: Clean CSV. Be mindful that though this will export as a CSV, it will not escape the fields in QUOTES. So if a cell has commas in them you will have to add quotes around them manually after you export it. Make your hired nephew look through all the CSV’s also, or make some clever regular expression. Also, the GoDaddy SQL Server web admin will give you errors when the fields don’t match up, so you could fix as the errors come up.

Step 5: Import CSV to GoDaddy. Back in your GoDaddy SQL Server web admin, there is a CSV File Importer that will import data for a specific table. Remember, if you have foreign key constraints, you will get an error if the parents are not uploaded first, and a child is pointing to a row that does not exist. So upload the most general tables first, then all the look up tables so all the foreign key restraints are satisfied.

That should be able it. Hopefully you experienced minimal errors. And don’t forget to update you web.config file’s SQLConnectionString to point to the new database. The new data source will be the full name of the database you are connected to in the GoDaddy SQL Server web admin (i.e. dbname.db.12345.hostedresource.com).

<connectionStrings>
		<remove name="SQLConnectionString"/>
		<add name="SQLConnectionString" connectionString="Data Source=dbname.db.123456.hostedresource.com; Initial Catalog=database_name; Persist Security Info=True;User ID=database_user;Password=database_password;Connect Timeout=200; pooling='true'; Max Pool Size=200"/>
</connectionStrings>

Tags: .NET, IT, sql

This entry was posted on Monday, August 3rd, 2009 at 10:27 pm and is filed under Misc. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.