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.
Ok, PHASE 2: Importing data, OR uploading to a server that didn't work well with the above procedure (if you pick the later, keep in mind things like foreign keys and constraints won't be imported. This only gets the tables/data).
Step1: Make sure everything is done up to Step 7 above.
Step 2: Run the DTSWizard. This is only available if you have SQL Server Express Service Pack 1 installed! (thanks for Caddre on that one! I would have never noticed)
Thats the default install location.
Step3: Connect to your local SQL Server Express instance. Make sure to select your -source- database (the one we attached!)
Step 4: Hit Next, then connect to your -remote- database (the one your host gave you the login and password to! Same way as when you connected to it in the SQL Management Studio Express tool). Sorry, I had to blank out part of the IP :)
Step 5: Select to import tables. You could do it with a query too (versatile, no?)
Step 6: Select the tables you want to import (this would work no matter the datasource! Access, SQL Server, MySQL, Postgres, Excel, whatever!). You can rename the destination table if you want. If you pick a name that already exists, it will overwrite/insert
the data (I think...been a while). If you do that, remember that you have to deal with foreign keys and constraints! So if you import a Detail table before importing a Master, you will get errors for violating your constraints. So you might have to do it in
multiple operations. If the table doesn't exist, however, it will just create it! If you need more advanced option, use the Edit Mapping button.
Ahh, its available separately? When you install the full SQL Server 2005 Express SP1, you have an option to install client tools, it gets installed there, too. Its how I got it.
Good :) Now if there's another discussion like the one that started this (Its like...the 4th one I see on this board or something?), we can conclude it by just linking here, yay!
THANK YOU SO MUCH! I was struggling with this last year for about a week and then I gave up and just created a db at the web host. Now I needed to transfer a few thosand rows and realised I really needed to get this to work, so I've tried another 20 hours,
and then I found this tutorial...
I inverted your first post's directions to download thes sql from my remote db to my local computer and then created a new db and executed the code. Now that I had a copy of it, I dared to try using DTS to upload some data from my local Excel document. And
it worked!
I became really depressed when reading the 100-post long thread with 30% people asking questions, 30% people answering other questions, 30% people saying it's impossible to write such a tutorial, and 10% people just nagging. ...and then there was your post.
THANK YOU!
BTW, my web host has only MSSql Server 2000, and I just want to report that it works like a charm (just set 2000 instead of 2005 in the various wizard panes - also, I don't have many fancy things, just plain tables).
shados
Star
12285 Points
2229 Posts
HOW-TO: Database from VWD to Shared Host
Nov 07, 2006 10:55 PM|LINK
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!
shados
Star
12285 Points
2229 Posts
Re: HOW-TO: Database from VWD to Shared Host
Nov 07, 2006 11:47 PM|LINK
Ok, PHASE 2: Importing data, OR uploading to a server that didn't work well with the above procedure (if you pick the later, keep in mind things like foreign keys and constraints won't be imported. This only gets the tables/data).
Step1: Make sure everything is done up to Step 7 above.
Step 2: Run the DTSWizard. This is only available if you have SQL Server Express Service Pack 1 installed! (thanks for Caddre on that one! I would have never noticed)
Thats the default install location.
Step3: Connect to your local SQL Server Express instance. Make sure to select your -source- database (the one we attached!)
Step 4: Hit Next, then connect to your -remote- database (the one your host gave you the login and password to! Same way as when you connected to it in the SQL Management Studio Express tool). Sorry, I had to blank out part of the IP :)
Step 5: Select to import tables. You could do it with a query too (versatile, no?)
Step 6: Select the tables you want to import (this would work no matter the datasource! Access, SQL Server, MySQL, Postgres, Excel, whatever!). You can rename the destination table if you want. If you pick a name that already exists, it will overwrite/insert the data (I think...been a while). If you do that, remember that you have to deal with foreign keys and constraints! So if you import a Detail table before importing a Master, you will get errors for violating your constraints. So you might have to do it in multiple operations. If the table doesn't exist, however, it will just create it! If you need more advanced option, use the Edit Mapping button.
Step 7: Just execute it now.
Then hit finish
Final Step: And its transfering! Tadah!
Enjoy your data!
ianmoore
Participant
870 Points
174 Posts
Re: HOW-TO: Database from VWD to Shared Host
Nov 08, 2006 10:47 PM|LINK
Thank you for this valuable tutorial. [8-|]
ianmoore
Participant
870 Points
174 Posts
Re: HOW-TO: Database from VWD to Shared Host
Nov 10, 2006 04:32 PM|LINK
if you want script for SQL Server 2000 instead of SQL Server 2005
in the first part of this FAQ when scripting dont click on the check box 'script all objects in the selected database'
this will bring up many options including scripting for SQL Server 2000 or 2005
ianmoore
Participant
870 Points
174 Posts
Re: HOW-TO: Database from VWD to Shared Host
Nov 10, 2006 07:39 PM|LINK
The Service pack 1 for SQL Server Express mentioned in the second part of the tutorial is this TOOLKIT file:
Microsoft SQL Server 2005 Express Edition Toolkit SP1
http://msdn.microsoft.com/vstudio/express/sql/download/
then the DTS wizard is available
shados
Star
12285 Points
2229 Posts
Re: HOW-TO: Database from VWD to Shared Host
Nov 10, 2006 08:33 PM|LINK
ianmoore
Participant
870 Points
174 Posts
Re: HOW-TO: Database from VWD to Shared Host
Nov 10, 2006 08:44 PM|LINK
I have just uploaded my database succesfully!
both database STRUCTURE using the first part of the tutorial
and also the DATA using the second part.
Thank you once again for this very clear tutorial.[cool]
shados
Star
12285 Points
2229 Posts
Re: HOW-TO: Database from VWD to Shared Host
Nov 10, 2006 08:46 PM|LINK
ianmoore
Participant
870 Points
174 Posts
Re: HOW-TO: Database from VWD to Shared Host
Nov 12, 2006 10:39 AM|LINK
The Tutorial above will upload your DATA and STRUCTURE of an SQL database.
once you have your database up online you will then need to change your 'CONNECTION' to the database
you will want to FTP your website and then make your website CONNECT to the database.
this link shows you how:[;)]
http://forums.asp.net/thread/1460858.aspx
pettrer
Participant
970 Points
469 Posts
Re: HOW-TO: Database from VWD to Shared Host
Mar 15, 2007 01:41 PM|LINK
Dear Shados,
THANK YOU SO MUCH! I was struggling with this last year for about a week and then I gave up and just created a db at the web host. Now I needed to transfer a few thosand rows and realised I really needed to get this to work, so I've tried another 20 hours, and then I found this tutorial...
I inverted your first post's directions to download thes sql from my remote db to my local computer and then created a new db and executed the code. Now that I had a copy of it, I dared to try using DTS to upload some data from my local Excel document. And it worked!
I became really depressed when reading the 100-post long thread with 30% people asking questions, 30% people answering other questions, 30% people saying it's impossible to write such a tutorial, and 10% people just nagging. ...and then there was your post. THANK YOU!
BTW, my web host has only MSSql Server 2000, and I just want to report that it works like a charm (just set 2000 instead of 2005 in the various wizard panes - also, I don't have many fancy things, just plain tables).
Pettrer