Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

DTS to copy DB from Srv1 to Srv2

Posted on 2009-07-16
11
Medium Priority
?
303 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

926 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