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.
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? |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-05 : 12:46:52
|
Can't you do Backup/Restore? |
|
|
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? |
|
|
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 |
|
|
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? |
|
|
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 |
|
|
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. |
|
|
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. |
|
|
qman
Constraint Violating Yak Guru
442 Posts |
Posted - 2008-05-06 : 06:37:28
|
Rmiao, thanks for the info! |
|
|
|
|
|