Solved

DTS to copy DB from Srv1 to Srv2

Posted on 2009-07-16
11
300 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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

710 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