I would like to copy a database from server a (2008) to b (2012). Usually I just make a backup and restore it on target.
Having the feature -copy database wizard- would make my task a bit easier as I have to move quite a few dbs. I'm using the SMO method and I run into several issues I could solve but now I got stuck with the following error:
Event Name: OnError
Message: An error occurred while transferring data. See the inner exception for details.
StackTrace: at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()
InnerException-->User, group, or role 'x' already exists in the current database.
StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.SqlServer.Management.Smo.Transfer.ExecuteStatements(SqlConnection destinationConnection, IEnumerable`1 statements, SqlTransaction transaction)
at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
Operator: CH\a
Source Name: b
Source ID: c
Execution ID: d
Start Time: 26.07.2091 11:30:57
End Time: 26.07.2091 11:30:57
Data Code: 0
Inside the wizard I opted to NOT copy the login objects or anything else. So to my understanding it should do a "plain" copy of the database and nothing else.
If you have any suggestions on why this error occurs, I would be very grateful!
On SQL Server, Logins are not the same thing as Database Users. A Login grants access to the server, and may be able to access multiple databases depending on the permissions assigned, whereas a Database User can only be granted access to particular things within their database.
In addition to setting transfer.CopyAlLogins = false; you will also need to set
transfer.CopyAllRoles = false;
transfer.CopyAllUsers = false;