Data movement from one server to another

Question

Post reply

Data movement from one server to another

suneet.mlvy

SSC Eights!

Points: 964
More actions
June 26, 2013 at 6:56 am

#295730

Dear All,
How can i move data(only data) from one db server to another dbserver(both are on different machine).
Both data have same structure, size of source database is 8 GB.
on another database there is some mismatch in data so how can i make them in sink.

Viewing 8 posts - 1 through 7 (of 7 total)

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

psingla Hall of Fame Points: 3840 More actions · Answer 1

use import/export

or

write your own SSIS

or

BCP in and BCP out

Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/

Lowell SSC Guru Points: 323518 More actions · Answer 2

i would do a backup and restore as -a-new-database name so both sources are on a single instance, and then review the differences for either specific insert/update/delete, or a MERGE statement.

other possibilities include creating a linked server, and performing insert/update/delete or a MERGE statement on a per table basis to insert/update/delete any differences, but linked servers are going to fill tempdb up with copied data from the remote server to the local server, where the changes are made and then propagated.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

kapil_kk SSC-Insane Points: 21316 More actions · Answer 3

Lowell (6/26/2013)
i would do a backup and restore as -a-new-database name so both sources are on a single instance, and then review the differences for either specific insert/update/delete, or a MERGE statement.
other possibilities include creating a linked server, and performing insert/update/delete or a MERGE statement on a per table basis to insert/update/delete any differences, but linked servers are going to fill tempdb up with copied data from the remote server to the local server, where the changes are made and then propagated.

But Lowell when we do a backup and restore on another server then I faced problems related to collations. what to do in this scenario?

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Lowell SSC Guru Points: 323518 More actions · Answer 4

kapil_kk (6/26/2013)
But Lowell when we do a backup and restore on another server then I faced problems related to collations. what to do in this scenario?

collation differences can be a pain, but are not insurmountable.

you end up having to explicitly add collate statements when doing comparisons;

here's a crappy example,

--SELECTING CAPITAL 'A'

SELECT CHAR(65) COLLATE Latin1_General_CI_AS AS ONE,

CHAR(65) COLLATE SQL_Latin1_General_CP1_CI_AS AS TWO

INTO #MYSampleData

SELECT

ONE,

TWO,

CHARINDEX(ONE,TWO),

CASE

WHEN ONE = TWO

THEN 'MATCH'

ELSE 'NO MATCH'

END

FROM #MYSampleData

/*

Msg 468, Level 16, State 9, Line 3

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the charindex operation.

*/

SELECT

ONE,

TWO,

CHARINDEX(ONE COLLATE Latin1_General_CI_AS ,TWO COLLATE Latin1_General_CI_AS),

CASE

WHEN ONE COLLATE Latin1_General_CI_AS = TWO COLLATE Latin1_General_CI_AS

THEN 'MATCH'

ELSE 'NO MATCH'

END

FROM #MYSampleData

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

suneet.mlvy SSC Eights! Points: 964 More actions · Answer 5

psingla (6/26/2013)
use import/export
or
write your own SSIS
or
BCP in and BCP out

Thanks Dear,

Can u suggest me fastest way to do this coz i have too low down time(approx 1 Hr) and my database is huge.

suneet.mlvy SSC Eights! Points: 964 More actions · Answer 6

Lowell (6/26/2013)
i would do a backup and restore as -a-new-database name so both sources are on a single instance, and then review the differences for either specific insert/update/delete, or a MERGE statement.
other possibilities include creating a linked server, and performing insert/update/delete or a MERGE statement on a per table basis to insert/update/delete any differences, but linked servers are going to fill tempdb up with copied data from the remote server to the local server, where the changes are made and then propagated.

Thanks Lowell,

But problem is i can't restore source database at target server and looking for fastest way to solve it.

i had try Linked server but it take too much time to move data. 🙁

Lowell SSC Guru Points: 323518 More actions · Answer 7

if it's a single 8 gig table, BCP is going to be a better option.

you'll need to create the table DDL on the Target server(as a new name) Like TableName_Imported or something

then a pair of bcp commands to get the data out and then back in: in this case, i'm using weird characters for row and field delimiters in order to be sure they don't exist in the data, and preserver things like Carriage Returns & Line Feeds in any comments that might exist int eh data:

if the data contains any varbinary fields, you might run into some issues as well:

--using a super special 4 char row delimiter to be sure it doesn't exist in the data

--flags explanation:

-- -c = charcater data

-- -t"[||]" = field terminator

-- -r"[~~]" = row terminator

-- -T' = Trusted connection

--out

EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT EMAILTEMPLATESID, EMAILBODY FROM BFONRA.dbo.EMAILTEMPLATES ORDER BY EMAILTEMPLATESID" queryout c:\Data\bcpExample.txt -c -t"[||]" -r"[~~]" -T'

two examples of bringing the data back in, via BCP or BULK INSERT, which are both incredibly fast :

--in

EXECUTE master.dbo.xp_cmdshell 'bcp BFONRA.dbo.EMAILTEMPLATES in c:\Data\bcpExample.txt -c -t"[||]" -r"[~~]" -T'

--in via bulk insert

BULK INSERT EMAILTEMPLATES FROM 'c:\Data\bcpExample.txt'

WITH (

DATAFILETYPE = 'char',

FIELDTERMINATOR = '[||]',

ROWTERMINATOR = '[~~]',

FIRSTROW = 1

)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!