DTS to copy DB from Srv1 to Srv2

I need to copy a production DB from svr1 to svr2.

I created a new DTS Job to copy DB1 from svr1 to DB1 on svr2. Saved the package and scheduled it to run every day at 7pm.

It starts the copy, and fails at STEP1.

When I go into the Job details, the error is as follows:

...   DTSRun:  Executing...   DTSRun OnStart:  DTSStep_DTSTransferObjectsTask_1   DTSRun OnProgress:  DTSStep_DTSTransferObjectsTask_1; Scripting objects for Transfer; PercentComplete = 0; ProgressCount = 0   DTSRun OnProgress:  DTSStep_DTSTransferObjectsTask_1; Scripting Logins; PercentComplete = 0; ProgressCount = 0   DTSRun OnProgress:  DTSStep_DTSTransferObjectsTask_1; Scripting Logins : 0 Percent Completed; PercentComplete = 0; ProgressCount = 0   DTSRun OnProgress:  DTSStep_DTSTransferObjectsTask_1; 0 Percent Completed; PercentComplete = 0; ProgressCount = 0   DTSRun OnProgress:  DTSStep_DTSTransferObjectsTask_1; Scripting Logins : 3 Percent Completed; PercentComplete = 3; ProgressCount = 0   DTSRun OnProgress:  DTSStep_DTSTransferObjectsTask_1; 0 Percent Completed; PercentComplete = 0; ProgressCount = 0   DTSRun OnProgress:  DTSStep_DTSTransferObjectsTask_1; Scripting Logins : 7 Percent Completed; PercentComplete = 7; ProgressCount = 0   DTSRun OnProgress:  D...  Process Exit Code 1.  The step failed.

I have ensured that the SQLSVR and SQLAGENT services are runnning using Administrative credentials.

Any thoughts?

Thanks in advance.
sohilAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Anthony PerkinsConnect With a Mentor Commented:
It means that step is using an invalid/obsolete connection (D1\USR).  Find the connection, fix it and you should be good to go.
0
 
Anthony PerkinsCommented:
Without knowing what STEP1 involves, it is difficult to say.  Perhaps the srv2 exists and therefore is unable to overwrite, perhaps there are users using srv2 and it cannot be dropped, perhaps ...
0
 
sohilAuthor Commented:
acperkins:

Server1 has DB1 (production) and Server2 is off-site.

I created DB1 on Server2. Then, I started Enterprise Manager. In EM, I created a  new DTS package (Local Packages) "DB1Copy" with just 1 task (Copy SQL Server Objects) with the following settings:

Source Server: Server1
Source DB: DB1
Security: DB Authentication (User: sa)

Destination Server: Server2
Destination DB: DB1
Security: DB Authentication (User: sa)

Task Options: (took default options).

I also made sure that SQLSRVR and SQLAGENT srevices are running with Administrative priviledges.

Thanks in advance for your help.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Anthony PerkinsCommented:
Have you tried running the package under your own credentials, as opposed to using the account used by MS SQL Agent when running it as a job?

Have you considered adding logging to the DTS Package so that you can get more detail as to the error ?
0
 
sohilAuthor Commented:
I tried to change the credentials. When I selected Windows Authentication in the package, the username and password is greyed out.
0
 
Anthony PerkinsCommented:
That is not what I meant.   If you run the DTS package it executes under your login credentials.  If you run the DTS Package as a SQL Server Agent job then it is using the SQL Server Agent account.  So here it goes again:
Have you tried running the DTS package without using a Job?

0
 
sohilAuthor Commented:
If you mean to right-click on the package and select Execute, then I have. It fails.
0
 
Anthony PerkinsCommented:
Actually no, I meant from within the DTS package select Execute that way you may have an inkling as to the actual task that is failing and the reason.

Also and again, please consider adding logging to your DTS Package, it will provide a success/failed status and the reason why it failed.
0
 
sohilAuthor Commented:
acperkins

Wow, you really opened my eyes. I think I enabled logging the right way.

I ran the job manually and I can see log entry in the DTS Package. Here is what it says:

Step Error Source: Microsoft SQL-DMO (ODBC SQLState: 42000)
Step Error Description:[Microsoft][ODBC SQL Server Driver][SQL Server]Windows NT user or group 'D1\USR' not found. Check the name again.
Step Error code: 80043C29
Step Error Help File:SQLDMO80.hlp
Step Error Help Context ID:1131

D1 is an old DC that no longer exits. Which means D1/USR can't exist. But, what does that have to do with the copy command?

Thank you very much.
0
 
sohilAuthor Commented:
acperkins:

I am still flabbergasted. I was able to make it work when I unchecked (disabled) all options in the 'Security Options' section in the 'Advanced Copy Options' window.

I finally figured out how you are right. So, this works when I disable the following:
- Copy database users and database roles
- Copy SQL Server logins (Windows and SQL Server logins)
- Copy object level permissions

How would I find this out?
0
 
Anthony PerkinsCommented:
>>How would I find this out?<<
I really could not say, I have never had the need to use that task.  However just cursory look through Google shows that most users recommend disabling those same Security check boxes.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.