Database Publishing Wizard SQL 2008

Brenden said 2 years, 2 months ago:

Based on the links below, how is one suppose to develop a website locally and restore a SQL 2008 database to the hosting plan? According to the first link SQL 2008 does not support DPS or DPW so that rules out every way listed below. I have over 800 different objects (tables, sp’s, views, etc.) do they all have to be exported and imported or scripts ran?

Any help would be appreciated.

What is URL for Database Publishing Service help document:

http://help.godaddy.com/article/3440

Restore Limitations: http://support.godaddy.com/help/3411

Restoring a SQL Server Database from .bak or .mdf:

http://support.godaddy.com/help/4504

Publish using the Database Publishing Wizard:

http://products.secureserver.net/products/hosting/PublishingWithDPW.htm

christianh said 2 years, 2 months ago:

@Brenden

Due to the restrictions that you are already aware of, you would need to import the content by following the instructions in this help article:

http://support.godaddy.com/help/article/1393

–Christian

Brenden said 2 years, 2 months ago:

Thanks Christian. And I was afraid of that. Out of the 800+ different objects I have 500+ of those are tables with data in them that could have changed. Looks like it will be a long, long weekend.

Can you tell me or do you know what the restriction of the DPW and SQL 2008 is? Is it a GoDaddy restriction or is it a MS restriction? I thought I read articles stating SQL 2008 was able to utilize the DPW but might have been wrong.

christianh said 2 years, 2 months ago:

I have reviewed this with our Advanced Hosting Team and have actually gathered more information for you. Using DPW and DPS is a restriction that we have in place. However, you can use SQL Server Management Studio Pro in order to publish from your local system.

–Christian

Brenden said 2 years, 2 months ago:

Do you mean SQL Server Pro vs. SQL Server Standard? I didn’t know the management studio (SSMS) had a pro or non-pro version. I only have SQL Server Standard.

Brenden said 2 years, 2 months ago:

Here are some instructions on what I was able to do using other tools to restore a SQL 2008 database to GoDaddy hosting. I suggest these be reviewed, tested and posted on your Help site as a solution for SQL 2008 users.

Directions to restore your local SQL 2008 database to your GoDaddy hosting using Visual Studio 2008 and SQL Server Management Studio 2008.

1. Connect to local database in Visual Studio 2008 Server Explorer.
2. Right click the database and click Publish to provider.
3. Click Next on the welcome screen.
4. Ensure the proper database is selected and Script all objects in the selected database is checked and click Next.
5. In the Select an output location, check Script to file and choose your location and file name to store the t-sql file on your local computer and click Next.
6. Choose the defaults on the advanced features (True, True, SQL Server 2008, Schema and data) and click Next. If you do not choose Schema and data, your data will not be input!
7. Review your selections and click Finish.
8. After the process completes, open SQL Server Management Studio (SSMS) and connect to your hosted database with GoDaddy.
9. Open the .sql file saved in step 5.
10. Make sure you choose the correct database in the dropdown list
11. Execute the t-sql. Depending on the database size it could take anywhere from 1 to 60 or more minutes. (my 40MB database took 15 mins.)

Brenden

nicholek said 2 years, 2 months ago:

@Brenden

Thank you very much for the additional information. Let us know if you have any other questions or great tips!

-Nichole

giru said 1 year, 1 month ago:

@Brenden

Thank you very much for your support but what is step 8 saying

Brenden said 1 year, 1 month ago:

@giru

In your Hosting Control Center where you manage your databases, if you have chosen to access your database with an external address you can simply add that URL into SSMS as a server name and connect to it without using the web tool. It’s easier since most of us use it anyway and the web interface isn’t that nice to work with in my opinion, it reminds me of the SQL 2000 interface, bad.

For instance here is an example of a host name: dbname.db.123456789.hostedresource.com. In SSMS you’d enter that as your server name, then select SQL Server Authentication, then enter your username and password combo and you can connect to your DB with godaddy from your local machine.

giru said 1 year, 1 month ago:

@Brenden

Thank to give your valuable time,
I am having SSMS 2008 not SSMS 2005 which is not supported by MDPW.
Please give me the steps to store my local database to godaddy 4GH shared hosting.
….

Brenden said 1 year, 1 month ago:

@guru, the above instructions are for SQL Server Management Studio 2008 and Visual Studio 2008 so I’m confused as to what you want different.

giru said 1 year, 1 month ago:

@Brenden
Thanks for reply, what this step saying?
“After the process completes, open SQL Server Management Studio (SSMS) and connect to your hosted database with GoDaddy.”
Please give instructions to perform this step.
giru

timb said 1 year, 1 month ago:

@giru

For steps on using SQL Server Management Studio to connect to your database, you can use this help article:

Connecting Remotely to Shared Hosting Databases

Tim B

giru said 1 year, 1 month ago:

@ timb
I am using following
1. open sql management studio.
2. Right click on selected database and click on Tasks>Publish using web service
3. A dialog box open.
4. select the database. Click on next.
5. select object type as Tables and then click next.
6. Select all the tables. Click next.
7. then select an output Location dialog open.I clicked on manage button
8.New dialog box as hosting providers open. I clicked on new button.
9.Then again new dialog box open named as “Provide configuration for new provider”
In this I wrote Name as “Hosting Publish service.”
web service address =
“https://sg2nmssqladmin.secureserver.net/2008/9/scripts/2005/framespage.aspx?uniqueDnsEntry=mydatabasehostname”
UserName = godaddy username
Password = godaddy password
In “available databases” I clicked on new
then again new dialog box open
In this I used
server name = My database host name
Database name = My database name
Database Authentication
User name = database username
Password = database password, then click OK
then click next I received following error.
“TITLE: Test failed
——————————

Client found response content type of ‘text/html; charset=utf-8′, but expected ‘text/xml’.
The request failed with the error message:
–
<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.01 Frameset//EN”>
<html>
<head>
<meta http-equiv=”Content-Type” content=”text/html; charset=iso-8859-1″>
<meta http-equiv=”Content-Language” content=”en-FX”>
<title>SQL Server Web Admin</title>
<meta name=”ROBOTS” content=”noindex, nofollow”>
</head>
<frameset rows=”25,*”>
<frame name=”Header” frameborder=”0″ noresize scrolling=”NO” src=”../2005/header.aspx”>
<frameset cols=”220, *”>
<frame name=”NetTree” src=”../2005/treecon.aspx”>
<frame name=”Content” src=”../2005/default.aspx”>
</frameset>
<frame name=”FrameContent” frameborder=”0″ noresize scrolling=”NO” src=”../2005/frameset.aspx”>
</frameset>
</html>

–. (System.Web.Services)

——————————
BUTTONS:

OK
——————————
“
8.

What is it
NOTE- I am using MSSQL 2008 on local and at godaddy 4GH hosting
Thanks for giving me your valuable time.

Brenden said 1 year, 1 month ago:

@giru,

Please read steps 1 and 2 more carefully. This process can be done but you have to use 2 different applications (Visual Studio 2008 AND SQL Server Management Studio 2005 or 2008 will work)

Steps 1 and 2 are in reference to connecting to your local database via Visual Studio NOT SSMS.
1. Connect to local database in Visual Studio 2008 Server Explorer.
2. Right click the database and click Publish to provider.