Forums: Back End:

I want to set up a copy of a database, which is on a client's server, onto my GoDaddy site so I can tweak it in private.

The client's server is running MSSQL 2005 and I can connect to it via Remote Desktop, GoDaddy runs the same, but the interface is web based.

I need to copy tables and stored procedures. There is data there, but the data doesn't matter.

I just don't want to have to recreate the whole damn thing by hand - if I could export it as CREATE TABLE... type commands I guess I could run those in the GoDaddy interface and be done.

I just can't find any option to export the database like that?

yeah, you can script the entire database to a script and run that on your server which will recreate the entire schema (tables, sp's etc) without the data.

In SQL Management Studio right click the db > Generate Scripts and follow the wizard.

aaaaaah there it is - tucked away in the Tasks bit of the right click menu.

Cheers Jamie

ah, I've decided now that I do need to copy the data after all.... but I can't find how to do that... is there an option to output the data to nice SQL statements I can just copy and paste to populate my destination DB on GoDaddy?

No.

The easiest way with only a web interface would be to use SELECT INTO ( assuming that your SQL server is publically available ( ie, you can connect to it from an entirely different network.

Else, export the data as a CSV and import using SQL in the web interface..

Or, hopefully, the GoDaddy SQL server is accessible from outside, and you can connect to it using SQL server Management Studio Express from the client machine, and once again, use SELECT INTO to transfer data.

There is another way, called BCP, but I cant recall if it is supported by 2005.

You could use the SQL Server Import and Export Wizard for the data transfer (although I don't think this is included with the Express version...)

Right click the DB > Tasks > Export data and follow the wizard. It will auto map your columns for you so it should be pretty painless.

The other option is to backup the database and restore it (Again under Tasks menu i think). This has the advantage of recreating the schema and data.

Although im not sure you'll have access to this from a web interface.

i would doubt you may have that access from the web interface, it would probably be difficult given the size of the .bak file. but that being said,t there's got to be a way to do it.

the import/export tool should not allow you to access remote servers.

the other bad part with import/export in mssql 2005 is that many of the advanced settings for tables are not replicated when you carry the data over - you must modify the sql query to include such statements (such as foreign key dependencies, indexes, etc). that is a real pain in the ass change from 2000 - 2005

you could generate a script on the current database that goes through all the tables and generates the create table script for you. you could then loop through all the stored procedures and generate that script as well. it would save you the time of right click - script create to clip board step over and over again. then there's having to open each stored procedure again and again.