Wednesday, March 21, 2012

DTS Copy SQL Server Task

I want to copy a database on a remote server to a local instance of SQL Server. I created a package that has the Copy SQL Server Task in it, but it seems to fail half way through every time i execute it.

I can view the local machine error log which simply reports that the table transfer was attempted.. but no explanation of why it fails. I don't have access to the host error log files either, so I have no idea why it fails.

I once almost got the 'Copy SQL Server Task' to work, but it would create duplicate records in the destination database, which I didn't want. It seems choosing the intuitive options like 'replace existing data' and choosing the table I want copied over.. does not work. I am not 100% sure the 2 databases are referentially identical either.. so it would be nice to know how to do a copy by first deleting the entire database on the destination machine, then recreating it all.

Can anyone give me some advice on which options I need to choose to achieve a proper database copy? Or, if you could show me a tutorial online that could explain how all the options work, and how I can debug what's causing my current method to fail, would be nice.

BrentWell I got it to work. It turns out that you can double click the error message to get some more details as it occurs. Strangely every account I had tried to use on my local server did not have permission to do the copying.. but only for certain attributes. So, basically by trial and error, I selected the right configuration to only copy the tables to the destination database (the accounts seemed to have permission to do certain activities).

It sure would be nice to find some info on the meaning of all the options in help or online somewhere.. if anyone knows of such a resource, they should post it.|||Thanks me!

I forgot how to view DTS backup log errors, and found my own old post to show me... double click the error in Ent. Manager!

This is what i needed to fix the problem!

No comments:

Post a Comment