Solved

DTS to copy DB from Srv1 to Srv2

Posted on 2009-07-16
11
297 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
 

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

939 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now