0

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!

Dale K
  • 25,246
  • 15
  • 42
  • 71
Daniel D
  • 57
  • 9

1 Answers1

0

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;
AlwaysLearning
  • 7,915
  • 5
  • 27
  • 35
  • Thanks for the answer. - This probably means I cannot use the wizard without first removing existing matching users/roles on side a or b, right? At least I didn't find the the option to not transfer Roles and Users – Daniel D Jul 26 '19 at 11:25
  • If the wizard doesn't allow you to untick Copy All Roles and Copy All Users then, yes, you'd have to delete the database users in the target database before the transfer. The problem there, though, is that the database user(s) will the `GRANT`/`REVOKE` privileges that were previously applied to them. Before the transfer you'll probably want to generate a script for all the users in the target database that recreates them and along with their `GRANT`/`REVOKE` privileges which you can execute afterwards. – AlwaysLearning Jul 26 '19 at 13:05