I'm not sure where to post this question; so please forgive me if I have post this in the wrong place.
I am trying to figure out what is the best practice for table(s) refresh from one server to another. For example, I have Development / Testing going to Production (for deployment) and sometimes from Production to Development/Testing server for testings.
I have thought of / tried
1) thought of using backup / restore. However, sometimes I just need a refresh of data from a single table or a handful of tables. I don't want to overwrite all the sp, functions, etc.
2) I have tried Red-Gate's SQL Data Compare. I didn't really like it (from production to development / testing) because it generates a whole bunch of transaction logs and I was advised not to shrink the log file. http://www.sqlservercentral.com/articles/Administration/64582/
3) I have tried the manual
i) drop constraints
ii) truncate table
iii) Import by going to Start, All Programs, Microsoft SQL Server 2008, Import and Export Data (32-bit)
Option 3 never got finished because I had so many errors. I'm not exactlly sure how to import / export using SSMS2008 on a MSSQL2000.
Plus if I go to Start, All Programs, Microsoft SQL Server 2008, Integration Services, Execute Package Utility, everything in the new window is grayed out.
So here I am, asking the experts ... what is the best approach to refresh data from different servers?