As part on mu final year project in college I was required to setup and interactive town web page.
I needed a strong and fast database for use with the webpage, I chose SQL Server 2005 Express edition for the page. I have spent many hours getting the database just so and was ready to upload it to my host a few days ago.
I logged onto the host's database server with the uid and pss I was given. From what I had found out from the internet and my lecturers, getting the database onto the server would only take a few simple steps. NO, not the case at all.
Problem
I could not figure out how to migrate the data from my database to the database my host had given me.
I logged into the Database host with MS SQL server management studio 2005 Express.
I right clicked on my database and looked for the import/export option... It wasn't there; it wasn't in the tasks sub menu either.
I was boggled, Why wasn't there an option for me to upload my database? What was Microsoft’s idea here? They give you all the tools you need but no functionality to upload your work... What's that all about?
After an extensive Google search I found one possible solution to my problem.
Solution 1:
I could restore the database on the server with the backup(.BAK) file of my project's database. This would be effectively the same as the import/export option I was assuming the program would have.
SO I created a backup on my database and logged onto the host server, I right clicked my db and clicked restore from backup option. I was presented with the E,F and D drives of the server computer but not my local computer. There was not option for me to navigate to my local computer at all. The only way I could restore from my back up file was for me to get the file onto the server somehow. With the customer support of my Web Host being so poor I doubt they would upload the .bak file any time soon.
I needed another way of getting my DB uploaded.
I tried to output my database to an sql query and then run that on the hosted db, I couldn’t get this to work correctly and neither could I get the records to go with this. I am new to MS SQL so I'm sure that this could have been done. However I opted for the easy option.
Solution 2
EMS DATA PUM 2006 for SQL Server
I downloaded a trial version of this program to help me get my DB online. The program promises to migrate all the tables and records from one DB to another. As this was only a trial version it would only copy over 5% of the records in the tables. However, it did set up all the tables on the host and that was one of the main problems for me because there were 20 or 30 tables to upload.
MY next step was to attach the hosted DB to Visual Studio .net. Now that I had the two databases attached to visual studio I created some code to set up Data Tables for each table in the local database. For my local database I set up table adapter method which took a parameter for the row and returned the record details of that row. Also in my dataset I created table adapters for each of the tables that had been uploaded by Data Pump and removed the records (5%) that it had put in. I set up INSERT SQL Queries on the table adapters for each of the tables. The code would then call these tableadapter.INSERT methods passing in the strings from Get methods that had been created by the local database earlier in the loop. This all happened with in a loop that would run for as long as i(row) was equal to or less then the row count of the local database table being used.
In this way I managed to transfer all my records into the hosted database. I'm pretty sure, in fact I’m positive that there are better ways of doing this but this is all I could think of at the time.
I probably could have saved a lot of time if I had just downloaded a crack for the data pump software but I wanted to do it via the code anyway cause it allowed me greater control over what was copied over and what wasn't.
I will go through this post again some day and improve the methods described but I just want to throw up an initial description first.
Hopefully my next post will shed some light on how exactly how to get the ASPNETDB database that attaches to MS's login controls in visual studio to work on your hosted web page. As of this moment I have no Idea how this is done seeing as there are no connection strings in the webconfig file and there is no access the functionality behind the login controls. I guess this make it more secure, but impossible to work with.
I hope this post helps someone out there with similar problems...
Thanks,
Billy