Copying Database Errors in Microsoft SQL Management Studio

While using the Copying Database task Wizzard in Microsoft SQL Management Studio I've run into the following issues. Also when the Wizard fails it says to check the Log on the destination server.  It means the Windows Application Event Log on the destination.

SQL Server Agent Service Permissions

If the Error in the event log looks like the following.

Date  2/9/2012 2:05:25 PM
Log  Windows NT (Application)

Source  SQLISPackage100
Category  (0)
Event  1074802950
User  NT AUTHORITY\NETWORK SERVICE
Computer  DestSQLServer.domain.com

Message
Event Name: OnError
 Message: Failed to connect to server SourceSQLServer\NamedInstance.
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 'NKU\DEVPHAROSSERV1$'.
StackTrace:    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
   at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
   at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
   at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()
   at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity)
   at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
 Operator: NT AUTHORITY\NETWORK SERVICE
 Source Name: SQLC2MAIN_MAINDB2_devpharosserv1_Transfer Objects Task
 Source ID: {4379DC83-9D53-4EE2-BF0F-F74234DF7898}
 Execution ID: {3C047A78-59C8-4AEC-8EF1-4FFDF5C372A2}
 Start Time: 2/9/2012 2:05:25 PM
 End Time: 2/9/2012 2:05:25 PM
 Data Code: 0

This is caused by permission problems when the SQL job is started by the SQL Server Agent but that Account the SQL Server Agent is running as doesn't have permissions on the Destination SQL Server to connect or copy the database.

I changed the Logon Account for the SQL Server Agent Service from Network Service to my own Active Directory account and then restarted service. Then restarted the Database move.

When the Move is complete you should change the Logon Account back to what ever it was when you started.

Copying Database Missing Stored Procedures

Event Name: OnError
 Message: ERROR : errorCode=-1073548784 description=Executing the query "sys.sp_addrolemember @rolename = N'RSExecRole', @m..." failed with the following error: "The role 'RSExecRole' does not exist in the current database.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
 helpFile= helpContext=0 idofInterfaceWithError={C81DFC5A-3B22-4DA3-BD3B-10BF861A7F9C}
StackTrace:    at Microsoft.SqlServer.Management.Dts.DtsTransferProvider.ExecuteTransfer()
   at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
   at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferExtraObjectsViaSmoTransfer()
 Operator: NKU\towlesd
 Source Name: CDW_SQLC2MAIN_MAINDB2_DEVPHAROSSERV1_0
 Source ID: {E0FD95A2-A75E-4BCC-9A2A-7FD76F61F73F}
 Execution ID: {04283ACD-30DF-43E4-B3FB-50CE71DE37D7}
 Start Time: 2/9/2012 2:47:44 PM
 End Time: 2/9/2012 2:47:44 PM
 Data Code: 0

This error was resolved by included Stored Procedures in the Wizard's  Select Server Objects. I'm not sure of all the implications of choosing this option but in my case I was just getting a devlopment server up. If this is for a production you should likely do some further research on the subject.