1、添加连接
EXEC sp_addlinkedserver 'xiaoming','','MSDASQL',NULL,NULL,'DRIVER={SQL Server};SERVER=10.112.19.xx;UID=UserName;PWD=password;'
2、利用连接生成脚本
exec sp_MSforeachtable @command1="print 'select * into ? from xiaoming.CCOS_BusinessDB.?'"
3、利用脚本在本地执行
参考
http://www.cnblogs.com/yunhuasheng/archive/2008/09/16/1291768.html
opendataset参考
http://www.cnblogs.com/OpenCoder/archive/2010/03/18/1689321.html