Solved

DTS to copy DB from Srv1 to Srv2

Posted on 2009-07-16
11
298 Views
Last Modified: 2013-11-30
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.
0
Comment
Question by:sohil
  • 6
  • 5
11 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24884072
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
 

Author Comment

by:sohil
ID: 24905380
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24910409
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:sohil
ID: 24926875
I tried to change the credentials. When I selected Windows Authentication in the package, the username and password is greyed out.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24927776
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
 

Author Comment

by:sohil
ID: 24928322
If you mean to right-click on the package and select Execute, then I have. It fails.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24931056
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
 

Author Comment

by:sohil
ID: 24934818
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 24934891
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
 

Author Comment

by:sohil
ID: 24989471
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24990960
>>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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question