SQL Server 2005 - How to upload database to DASP?

Hi all,

For a new SQL Server 2005 account at DASP, how do I upload (deploy) my database from my development machine to DASP?

Thanks very much.

Tritim

 

Hi,
What tools will you be using on your end?
If it's the Express tools please take a look at this:
http://iis7testcom.web701.discountasp.net/membershipprovider/


Here's a simple video I created that shows how to connect to the remote SQL Server:
http://iis7testcom.web701.discountasp.net/connecting/connecting.html


I'll help with whatever you need, just fill me in.
Salute,
Mark

 

Mark,


I'm using SQL Server 2005 Management Studio on my development machine. I have a working database, with tables, stored procedures, triggers, etc., on my development machine.


I want to upload the database to a SQL Server 2005 account at DASP.


How do I do it?


Thanks very much.

 

Hi,
Connect to your local DB then right-click on the DB that has the tables you want to export.
You'll see the menu for the Import and Export tasks.
Should be easy from that point, if not let me know.
Salute,
Mark

 

Mark -


But what about the stored procedures, triggers, indexes, etc.? Doesn't SQL Server 2005 Management Studio's Import/Export data feature only copy the table structure and data but not the objects like indexes, triggers, etc?


Thanks.

 

What I normally do for this is create a set of scripts, from the same menu.
You can create the objects, data and relationships that way.
If you need specifics let me know, but it is pretty easy.
Salute,
Mark

 

Mark,


In Management Studio, I see two ways of scripting a database by right-clicking on the database:


1. Script Database As


2. Tasks/Generate Script


Which do you use? If you use #2, do you accept all the defaults?


Thanks very much.

 

Then things got ugly, for the past three weeks I've been fixing plumbing in this old house.
Have had to rip the walls open in places, one nightmare after another, if you can imagine.






Anyway, theseare the steps I'd take:

1. Right-click on a DB.
2. Select Tasks, then Generate Scripts.
3. When the wizard opens click Next, then chose the DB you want to work with.
4. Click Next then look over the options, make changes as needed.
   Notice scripting objects like Triggers will be OFF, you'll need to pay attention and make changes.
5. Click Next then select Tables, in the objects you want to include.
6. Now choose your tables and click Next.
7. You can now script this as a file or to the clipboard or to a new Query window.
   Normally the clipboard works for me but I do often run it again and save the same to a file.
8. Now generate the script and watch for errors.
9. At this point I connect to the remote SQL Server, open a new query window and drop the script in.
   You will of course need to edit the script, just take out the DB name since on your remote SQL Server it's given.

Mark - Thanks very much. That's what I needed to know.



Post Edited (Tritim) : 5/13/2008 5:01:51 PM GMT

 

I may make some videos for this.
Was going to last month then put them off to create SSRS and IIS7 videos for DASP.

Then things got ugly, for the past three weeks I've been fixing plumbing in this old house.
Have had to rip the walls open in places, one nightmare after another, if you can imagine.


Anyway, theseare the steps I'd take:

1. Right-click on a DB.
2. Select Tasks, then Generate Scripts.
3. When the wizard opens click Next, then chose the DB you want to work with.
4. Click Next then look over the options, make changes as needed.
   Notice scripting objects like Triggers will be OFF, you'll need to pay attention and make changes.
5. Click Next then select Tables, in the objects you want to include.
6. Now choose your tables and click Next.
7. You can now script this as a file or to the clipboard or to a new Query window.
   Normally the clipboard works for me but I do often run it again and save the same to a file.
8. Now generate the script and watch for errors.
9. At this point I connect to the remote SQL Server, open a new query window and drop the script in.
   You will of course need to edit the script, just take out the DB name since on your remote SQL Server it's given.

Once the scripts are run on the remote SQL Server I use the Import Export wizard as needed.
For some records, forums as an example, the wizards can do all the work just fine.
Hope that helps.
Salute,
Mark

 

markreyn,


That looks like what I need.


Thanks very much.


I wonder if it's supported here at DASP?

 

I use the database publishing wizard


http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en


Overview can be found here


http://weblogs.asp.net/scottgu/archive/2006/12/22/recipe-deploying-a-sql-database-to-a-remote-hosting-environment-part-1.aspx

 

Tritim,


I'm not certain that it is supported on DASP. You can always create an aspx page that runs the script, but it is much easier to simply use Sql Server Management Studio (SSMS), connect to your db, right click on db, create new query. In the query window, simply paste the script it generated (of which you can view in notepad to copy/edit it) and then 'Execute' the query (the script) against that db (just press the execute button). The nice thing about using SSMS is that you can validate your data in the same interface as well as step through any errors that may have occured in executing your script.


Mike

 

Mike,


How does your technique differ from the one by Mark (3 posts down in this same thread)?


Thanks very much.

 

Tritim,


Actually, the main reason I was posting was to answer your question as to the support of running .sql scripts from DASP. In addition, I thought the link was to an article that I used to guide me through. After following the second link, I really wish I would've had that about a two weeks ago. It is a great article.


The main difference in my method is thatI actually used the auto-generated aspnetdb.mdf database (the one with membership, roles, profiling, etc) to host my custom tables. I needed a relationship tied between the aspnet_users table and one of my customer tables. Rather than have two databases, I used the aspnetdb.mdf file - thus making the joins easier. This turned out to be a huge mistake (if using Sql Server Express) when it came to migrating the data to DASP. The work around I used was that instead of using the database publishing wizardsdefault selections, I had to script only my custom tables and objects(sprocs). You do this just as the tool starts up. In the Selectdatabase window there is a check box at the lower left corner (Script all objects in the selected database).


I originally used this method, but the aspnet membership tables completely hosed my site. So, Iunchecked thebox I just referenced and individually selected my custom data tables and sprocs.IthenusedSSMS to run this custom script against my DASP db. After doing that, I then used aspnet_regsql tool to add the membership tables to this database. It all depends on whether or not you decide to use membership. If you do, I'd highly recommend performing the steps as I've just run through.


Cheers,


Mike

 

Mike,


I'm not using membership yet, but expect to in the future. It'll be good to have your posting here to refer to.


Thanks very much.

 

markreyn,


That's what I needed to know.


Thanks very much.

 

I've used the database publishing wizard to script my local schema and data then connected to my DASP server using EM and run the script.

Never had a problem with it