Goal: Copy a database from a machine I don’t control to one I do using Transfer Database Task.
Web hosting scenario: If you don’t have sysadmin rights on the source machine, you can’t use offline transfering, which appears to use a detach-copy-reattach process.
On the otherhand, online tranfer wont work either, since you need a network share to both servers!
Patching: Ideally, you are at service pack 2 or later and probably even more ideally, the source and destination are at the same service pack level. I ran into an issue where I couldn’t copy from SQL2005RTM to SQL2005SP2 on account of SMO versioning problems.
Advice: The Transfer Database Task is really only good if you have full control over both source and target machines and both are on the same network.
Rebuttal: ”Yeah, but SSIS is supposed to be a corporate tool, not a tool for people who don’t own both machines” And as far as I can tell, SSMS uses the SSIS Transfer Database Tasks as it’s database copying mechanism.