Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 SSIS error when copying database to database

Author  Topic 

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-05-05 : 11:38:19
I am trying to copy one database to another using copy wizard for SQL Server 2005. The destination database is on another server/box.
I get the following errors when executing the SSIS package: "The job failed. The Job was invoked by User abcd. The last step to run was step 1 (abcd_0_Step).".


"Executed as user: BILLSVR\SYSTEM. ...ion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:30:55 AM Progress: 2008-05-05 11:30:56.81 Source: crmtest_BILLSVR_Transfer Objects Task Task just started the execution.: 0% complete End Progress Error: 2008-05-05 11:30:57.34 Code: 0x00000000 Source: abcd_abcd_Transfer Objects Task Description: Failed to connect to server crmtest. StackTrace: at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect() at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.OpenConnection(Server& server, ServerProperty serverProp) InnerException-->Login failed for user 'abcd\abcd$'. StackTrace: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.... The package execution fa... The step failed."


Can this be done? Is there something that needs to be set security wise for this to work?

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-05-05 : 11:49:47
Is it possible to copy from SERVER to SERVER?
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-05 : 12:46:52
Can't you do Backup/Restore?
Go to Top of Page

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-05-05 : 13:00:34
Will a back up and restore copy everything including security and user/roles to the new database?
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-05 : 13:08:09
You will have to synchronize users(copy SID) in new server
Go to Top of Page

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-05-05 : 13:31:52
We are trying to use the copy database wizard which will keep everything "together". Can this be used, why am I getting the error mentioned in the initial post?
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-05 : 13:43:54
You have to either drop the destination database if it exists or overwrite the data files for it to work
Go to Top of Page

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-05-05 : 13:50:04
Thanks, we are doing a side by side upgrade rather than an in place upgrade.

Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-05-05 : 22:30:08
Copy database wizard does db detach and attach, that needs taking down source db. Backup/restore will bring everything in the db to target server. For sql login, you need create them on target no matter move db with wizard or backup/restore.
Go to Top of Page

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-05-06 : 06:37:28
Rmiao, thanks for the info!
Go to Top of Page
   

- Advertisement -