May 20, 2010 at 9:43 pm
Hi, I have not yet found a good way to copy a database table from one SQL Server to another. I've searched up and down online for an answer but none of the explanations make sense to me.
Most recently, I had three tables that I wanted to copy from SQL Server 2008 to another 2008 server. These two servers are not on the same network so I can't simply choose the other server as a destination.
The only thing that I can find to do is choose to export the database, pick a single table, and choose flat file as the destination. This means that I have to make a seperate text file for each table. Unfortunately the flat file only takes the field names and data, it does not notate the data types, allow nulls, or the max characters.
To get these missing characteristics, I can generate a "create to" script that I can save and then paste into the remote server. An empty table is created with the correct data types. Then I can import the flat file into tht empty table but I often run into conversion errors when the data tries to go from flat file back into the original data types.
Is there a better way? Can I export and then import tables while keeping data types etc? It is easy when I can authenticate across the network to both servers but hard when I am working with a flat file.
May 21, 2010 at 3:32 am
Is this a regular copy or a one off?
There's a number of solution.
If it's a one off you could use the BIDS
Create an SSIS package that copies it directly from one system to the other.
Or depending on a number of factors you could set up linked server to those tables.
May 21, 2010 at 10:32 am
Thanks for responding.
The challenge is that I can not connect from one server to the other.
Right now I use a two step process to export the data to a flat file and then generate a "create to" script that I can save and then run on the destination server to premake the table prior to importing.
Sometimes though I run into issues with putting the data back into the table from the flat file source.
I was just wondering if there is a better way.
May 23, 2010 at 4:12 pm
PHXHoward (5/21/2010)
The challenge is that I can not connect from one server to the other.
Question is: what is preventing the connection?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 23, 2010 at 5:40 pm
Seperate development and production domains.
May 25, 2010 at 7:46 am
You can backup the production database, restore it on development under a brand new name so as not to corrupt dev, and copy the tables necessary using insert devdbname..tablename (columns) select (values) from newdevdb..tablename. Personally, not sure what the issues your facing with a text file create and load. The table DDL on your dev server should be identical to production - otherwise how do perform true unit testing?
-- You can't be late until you show up.
May 25, 2010 at 8:46 am
PHXHoward (5/23/2010)
Seperate development and production domains.
Any particular reason not to allow read-only access on production to a development side "refresh" account?
Development environment usually holds a volume-scaled-down subset of production data, hard to imagine a shop that doesn't have a proven process in place to refresh such volume-scaled-down dataset.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 26, 2010 at 7:48 am
PaulB-TheOneAndOnly (5/25/2010)
PHXHoward (5/23/2010)
Seperate development and production domains.Any particular reason not to allow read-only access on production to a development side "refresh" account?
Development environment usually holds a volume-scaled-down subset of production data, hard to imagine a shop that doesn't have a proven process in place to refresh such volume-scaled-down dataset.
I agree with this.
Given that there is not a connection though, I don't know of a push-button, simple solution to your problem.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 26, 2010 at 3:15 pm
I have a similar setup that I can only get data off of production by using Remote Desktop Connection file transfer.
I use BCP to copy several tables from production to local files on production. Zip these files so they are smaller to transfer over to our development servers through a small internet pipe, then use BCP to put them into the tables on the dev server. A simple .bat script at each end takes care of everything.
- Michael
May 27, 2010 at 6:51 am
Have you tried OPENROWSET?
SELECT a.*
FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=servername;UID=userid;PWD=passsword',
databasename.dbo.tablename) AS a
This performs well and is convenient
May 27, 2010 at 9:32 am
Thanks for all the replies. I'll try these solutions.
I also found that choosing tasks --> generate scripts and choosing the option to script data, keys, constraints, etc allows me to get a script that can be copy/pasted into the destination server.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply