Hi. Since this has been a heated topic as of late (and i take the blame for part of it), I figured many people would benifit from a way to get their database on a shared host, so I took some screenshots of 2 different ways, both with different advantages and drawbacks (but can be used together to combine both!), using NOTHING but VWD and SQL Management Studio Express to get your database on your shared server!
This will be split in two posts. Scripting the database using SQL Management Express to upload the structure, and in a second phase, uploading data (or raw tables) from (almost) any data source to any other datasource.
NOTE: At any time, when I talk about the Object Explorer, if you don't see it, in SQL Management Studio Express, hit the F8 key!!
==========================
PHASE 1: Uploading your structure.
Requirements:
-VIsual Web Developer, with a project created that uses a database (MDF file in APP_DATA)
-SQL Server Express Service Pack 1 (Important!! It -has- to be SP1, and you have to install basically everything from it, client tools and all)
-SQL Management Studio Express
-A shared host with a database (It -has- to be SQL Server 2005 ) already created (Which means your host gave you: An IP Adress with optionaly a port number, a Database Name, a user name, and a password. This method will not work otherwise (obviously!).
Thats it!.
Ok, lets get started:
Step1: Make sure you have a MDF file and its log created from VWD. It would look like this (just in case):
Step2: SQL Server Express need to attach the database. It will only work from a system wide directory, so it won't if the MDF is in your My Document folder (which it probably is). So take the *.mdf file and the LDF file from your app_data directory, and copy paste it somewhere else. For simplicity, lets put it in C:\Database (create a new directory).
Step3: If you installed SQL Server Express with default settings, right now it will be running. If its not, just post a question in this thread, but its beyond the scope of this tutorial. Now, login. If its your only version of SQL Server installed, the SQL Management Studio Express (make sure thats installed to!) tool will have default settings that allow you to connect to it without any issue, like this:
So far so good? Now Connect. At this point, we can use all the goodies from SQL Management Express to, well...manage our database!
Step4: We need to -attach- the database to SQL Server Express (because right now its just a file with no meaning). In the Object Explorer in SQL Management Express, expend the tree, and right click Database. Pick Attach.
Step 5: Now, click the "Add" button and browse to your MDF file, which, again, must NOT be on a network folder, or in your My Document or something like that. To be safe, put it somewhere near the root of your C. I put mine in C:\database (there is the default SQL Server directory thats a better place, but lets keep things simple for this tutorial). When done, click OK
And after thats done, you should see something like this: (remember, its important when you attach that the LDF file is in the same folder as the MDF)
Step 6: Now our database is available with SQL Server, locally. It will have the original path as its name unless you changed it. Confusing as hell, but it will work anyway. As you can see in my screenshot, the -name- of the database was this long pointless path, because I forgot to change mine. If its your first time doing this, you probably will forget too :) It does no harm, however. Just remember it.
Step 7: Now, lets connect to our shared host! Sorry, I don't have a screenshot for this. In the object explorer (thats the thing on the left where you see your database(s), basicaly the screenshot above), look near the top. There's a button that says "Connect Object Explorer". Click it. The login screen is coming back. Now, enter the IP adress where it asks for the server name. If you were supplied a port number, it goes there too! Separate the IP from the port number with a comma. like this:
123.456.123.345,1234, where 123.456.123.345 is the IP adress, and 1234 is the port number. Thats your server name. Change authentication to SQL Server Authentication, and supply your user name and password. You will now be able to connect to your remote host! If its a large shared host, you will see a TON of databases! Browse to yours (thats why you need your host to supply its name, you probably don't have the right to create a new one), and expend its tree.
Step 8: This is where the fun begins: You should now have in the object explorer BOTH your local database, and your shared host, both expended. Now, right click on your LOCAL database (on its name). In the Tasks, the last option should be Generate Script, Click that:
Step 9: Now you will have a list of all your local databases. Pick the one you attached (in my case it has that weird C:\blah blah blah name).
As you can see, I have a lot of databases. Just pick your one (you probably wont have anywhere as many!), and check the "Script All Objects in the selected database" box. The Finish button will light up, click it:
Now you can confirm your operation. Hit Finish again.
The script is being generated. If you have a large database, this will take a long time! But its normal.
When you're done, you're gonna have a window open with your script. Thats a ton of code! Click in that window, and hit ctrl+A (select all). Then copy the text to clip board (Ctrl+C). We're ready to generate our database on our host!.
Step 10: Remember we put our remote/shared host in our object explorer earlier? Time to use it. Navigate to your database in the list (again, its probably long, pick yours! not someone else's!), right click the database, and pick New Query
In the new, blank window that opens, paste your script (ctrl+v). Now execute. This may take a while. Right click anywhere in the script, and pick execute:
If everything went well, you will see the following message in the output window:
Otherwise, you might get an error message. There are various reasons why you might, the most common being incompatible SQL server version (I did say this only worked (reliably) for SQL Server 2005!) There are various host specific permissions that could have been set to prevent you from importing everything. If that is your case, please post here, and I'm sure someone (or me!) will be happy to look into it. Most of these problems can be resolved by scripting LESS stuff (remember when we clicked finish after checking the "Script All Object" box earlier? Well, unchecking that allows you to select only the objects you want. Removing some might be necessary on some hosts.
Final Step: ENJOY! Your database structure is now on your shared host. You now only need to switch your connection string (thats beyond the scope of this tutorial, but search the forums, connection strings are explained in a million other posts and is well documented) accordingly, and you're good to go!
Note: This will NOT, however, import your data, only your database structure (tables, stored procedures, constraints, etc)
NOTICE: I am not perfect, so I'm sure there's an error or two in this. Feel free to ask questions or point it out.
Hope this helps!