copying data from one sql server to another

[pnj]

Howdy,
I have a copy of sql server 2000 on my test machine and I have a copy on the "live" server.

I need to transfer my existing DB to the live server but I'm not sure how to do it.

I've been looking at BOL but I haven't found anything that is helpfull.

any suggestions you have would be great.

Thank you

[techgnome]

Two options:
1) Use a DTS package.
Pro: Can be set up as a "job" in SQL Server
Con: Depending on the size, it can be time consuming

2) Detach the database, copy it to the new server, and attach it.
Pro: Can be done in a matter of minutes (depending on server capacity & workload)
Con: Has to be done by hand every time.

[pnj]

thanks

the DB is very small (ten tables, little data)

so time wouldn't be an issue.

can I just use the copy wizard?

thanks again!

[techgnome]

<shrug> I guess. I'm use to moving DBs of several G's, so I usually use the Detach/Copy/Attach method. But I don't see why the copy method wouldn't work.

One thing, is the data on the test machine going to replace the existing data, or add to it or....????
If it's going to replace existing data then copy will work ok. If not, then I would go the full DTS route (copy db is a form of DTS) and let DTS copy only new rows where the data doesn't already exist.

[pnj]

the tables i'm trying to copy don't exist on the other copy of sql.

someone just told me i need to make a .MDF and a .LDF file and that will work.

only i don't know how to create those files.....


does that make sense?

I'm not trying to overwrite data I'm trying to add a table that doesn't currently exist on one sql server that does exist on another.

[techgnome]

If all you want ot do is copy a table (and not the whole DB), then ignore the MDF LDF advise (those are the data file(MDF) and log file(LDF) that SQL server creates. they also relate to the detach/copy/attach method I mentioned.)

From Enterprise Manager, open up the DB you want the table to go to (the destination). Right-click, select "All Task", then "Import Data" It should be as simple as wakling through the wizard from there.

[pnj]

oh, ok...

thanks!