Best way to copy data?

Question

Best way to copy data?

fminns-1058143

Default port

Points: 1404
More actions
May 13, 2009 at 6:20 am

#384573

Hi all (SQL 2005)
I'm still learning the ropes here and have a question for you.
I have been asked a refresh a database, data only, from one machine (live) to another (test). Now, normally I would use REDGATE SQL Compare to do this, but it's not working.
So, what is the best way to do this?
Should I truncate all the tables in the target test database and import the data?
Is there a way to create a copy of the target db 'without' data as that would preserve the stored procedures etc and I could restore into that. In fact if I can do that REDGATE would still be an option.
Anyone pointers ?
Many thanks
Farren

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

SSIS would be my preferred method of doing this. You can create a quick import task in SSMS by right clicking on the on the Database to be refreshed, and going to Tasks and Import Data. Pick your tables, and then you can set for each table whether to append the data or drop the existing data.

Wilfred van Dijk SSCrazy Eights Points: 9003 More actions · Answer 2

There are a lot of options, but these wil depend on the amount of data and your network configuration. For example, you could:

- backup the source database and restore it as the destination database

- truncate the destinationtables and use a "insert into ... select * from {sourceserver}.{sourcedatabase}.{sourceschema}.{sourcetable}" (use tablock hint will speed up).This option might require a lot of logspace

- truncate the tables and use BCP export / import. This is a very fast option. You could keep the logfile small with the -b option (see documentation)

- if Redgate is not working, did you post a call?

Wilfred
The best things in life are the simple things

fminns-1058143 Default port Points: 1404 More actions · Answer 3

Thanks people, that gives me plenty to be looking into.

Re REDGATE, do you mean place a support call or just post something on the REDGATE forum?

I inherited all this stuff and am not sure if I can ghet official support for REDGATE.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 4

Redgate to date has been really good at supporting their products, so I'd give that a shot if you are having a particular problem with something. It is in their interests to support their products since that is how they sell newer versions of them.