If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > moving data

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Feb 2004
Posts: 490
moving data

We are moving data from one server to another. The table structures are somewhat different but data is bound to strict constraints from a business and security point of view.

So right now I've created the queries that produce the right data, but they are somewhat large. I'm also supposed to be able to bundle the whole lot and have it executed 'as is' on the production environment.

So I put each query in a separate file and I'm using sqlcmd for exporting. I made a script to execute all queries and dump the data into files. However, I'd rather use bcp instead so I can use format files for both the export and import. The binary data especially proves a bit of a hassle on the other side.

Do you guys have a best practice on this?

thanks,
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Aug 2004
Location: Dallas, Texas
Posts: 764
What version of sql server? DTS or SSIS packages are the way to go.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Feb 2004
Posts: 490
Thank you for your suggestions. We're using 2005 and 2008. Personally I'm not too keen on SSIS packages, but that's probably me: I've had some bad experience staying in control and finding out what's wrong. I'm more of a script person anyway.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Aug 2004
Location: Dallas, Texas
Posts: 764
Quote:
Originally Posted by Kaiowas View Post
Thank you for your suggestions. We're using 2005 and 2008. Personally I'm not too keen on SSIS packages, but that's probably me: I've had some bad experience staying in control and finding out what's wrong. I'm more of a script person anyway.
With SSIS you can have error log reporting which should give you a record for each failed import. I used this task to write out the specific records that didn't import so they could be corrected and resent.
Reply With Quote
  #5 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,417
PowerShell might be more your speed then. Depending on what conferences you've attended you might be able to access one of the SSIS versus PowerShell Smackdown presentations, but even if you can't do that you can at least review http://mem-pass.org/summits/2011/pdfs/BIA-100.pdf for some idea of what is possible.

-PatP
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On