Share via

Copy Database Wizard fails

Federico 0 Reputation points
2025-11-30T21:47:49.12+00:00

Hello folks,
My goal is to migrate DB from server A (MSSQL 2019 Standard) to server B (MSSQL 2022 Standard).
These two servers are two different VMs and they can communicate over network.

I would like to avoid, if possible, the "traditional" procedure: dump and import the dump.
I have seen that it is possible to migrate the database using the "Copy Database Wizard" functionality, with the option "Use the SQL Management Object method"

KB: https://learn.microsoft.com/en-us/sql/relational-databases/databases/use-the-copy-database-wizard?view=sql-server-ver17

The procedure fails, even though the SQL Agent is running on the target MSSQL instance. In addition, I changed the user running the MSSQL Agent service. I configured the same user to run the wizard. This account has "sysadmin" access.

OnInformation,VM2,COMPANY\my_admin,VM1_kathrein_local_VM2_Transfer Objects Task,{400FF9EC-A42D-4393-BCD3-D47842F004F0},{F949D4EF-D8F8-4E00-9DC7-EA7074528D93},29.11.2025 00:07:47,29.11.2025 00:07:47,0,0x,Transferring data to database FactoryDB from FactoryDB
OnInformation,VM2,COMPANY\my_admin,CDW_VM1_VM2_7,{DF71A845-A968-4FBB-9E4B-C88F29DB8997},{F949D4EF-D8F8-4E00-9DC7-EA7074528D93},29.11.2025 00:07:47,29.11.2025 00:07:47,0,0x,Transferring data to database FactoryDB from FactoryDB
OnError,VM2,COMPANY\my_admin,VM1_kathrein_local_VM2_Transfer Objects Task,{400FF9EC-A42D-4393-BCD3-D47842F004F0},{F949D4EF-D8F8-4E00-9DC7-EA7074528D93},29.11.2025 00:08:17,29.11.2025 00:08:17,0,0x,Cannot drop database "FactoryDB" because it is currently in use.
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()
   at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()
OnError,VM2,COMPANY\my_admin,CDW_VM1_VM2_7,{DF71A845-A968-4FBB-9E4B-C88F29DB8997},{F949D4EF-D8F8-4E00-9DC7-EA7074528D93},29.11.2025 00:08:17,29.11.2025 00:08:17,0,0x,Cannot drop database "FactoryDB" because it is currently in use.
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()
   at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()
OnProgress,VM2,COMPANY\my_admin,VM1_kathrein_local_VM2_Transfer Objects Task,{400FF9EC-A42D-4393-BCD3-D47842F004F0},{F949D4EF-D8F8-4E00-9DC7-EA7074528D93},29.11.2025 00:08:17,29.11.2025 00:08:17,0,0x,Database transfer failed for 1 database(s).
OnProgress,VM2,COMPANY\my_admin,VM1_kathrein_local_VM2_Transfer Objects Task,{400FF9EC-A42D-4393-BCD3-D47842F004F0},{F949D4EF-D8F8-4E00-9DC7-EA7074528D93},29.11.2025 00:08:17,29.11.2025 00:08:17,100,0x,Transfer objects finished execution.
OnTaskFailed,VM2,COMPANY\my_admin,VM1_kathrein_local_VM2_Transfer Objects Task,{400FF9EC-A42D-4393-BCD3-D47842F004F0},{F949D4EF-D8F8-4E00-9DC7-EA7074528D93},29.11.2025 00:08:17,29.11.2025 00:08:17,0,0x,
OnPostExecute,VM2,COMPANY\my_admin,VM1_kathrein_local_VM2_Transfer Objects Task,{400FF9EC-A42D-4393-BCD3-D47842F004F0},{F949D4EF-D8F8-4E00-9DC7-EA7074528D93},29.11.2025 00:08:17,29.11.2025 00:08:17,0,0x,
OnWarning,VM2,COMPANY\my_admin,CDW_VM1_VM2_7,{DF71A845-A968-4FBB-9E4B-C88F29DB8997},{F949D4EF-D8F8-4E00-9DC7-EA7074528D93},29.11.2025 00:08:17,29.11.2025 00:08:17,-2147381246,0x,SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Do you have suggestion for me?
Did you migrate DB successfully in the past using "Copy Database Wizard" feature?

Initially, CDW, create a new database on VM2, but it fails the tables copies; it fails to drop DB.
In short at the end of this wizard there is a fresh empty DB on VM2.

Thanks!

SQL Server | Other
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Q&A Assist
    2025-11-30T21:48:04.74+00:00
    AI generated content. This question contains an answer created with AI from Q&A Assist. Learn more

    It seems that the error you are encountering during the migration using the Copy Database Wizard is due to the database being in use at the time of the operation. The error message Cannot drop database "FactoryDB" because it is currently in use indicates that there are active connections to the database you are trying to drop or transfer.

    To resolve this issue, you can try the following steps:

    1. Disconnect Active Sessions: Before running the Copy Database Wizard, ensure that all active connections to the database are terminated. You can do this by using the following SQL command: 
         ALTER DATABASE FactoryDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
   ALTER DATABASE FactoryDB SET MULTI_USER;
      This will set the database to single-user mode, forcing all other connections to close, and then back to multi-user mode after the operation.
    2. Check for Replication: Ensure that the database is not marked for replication, as this can also prevent the database from being dropped.
    3. Use the Right Method: Since you are using the SQL Management Object method, ensure that this method is appropriate for your scenario. If the database is large or has many objects, consider using the detach and attach method instead, as it may provide better performance and avoid some issues related to active connections.
    4. SQL Server Agent: Make sure that the SQL Server Agent is running and configured correctly, as it is required for the Copy Database Wizard to function properly.

    If you continue to experience issues, you might want to consider using the traditional backup and restore method as a fallback option, as it is a reliable way to migrate databases without encountering connection issues.

    References:

    0 comments
  2. Erland Sommarskog 128.6K Reputation points MVP Volunteer Moderator
    2025-11-30T22:09:26.3533333+00:00

    I would like to avoid, if possible, the "traditional" procedure: dump and import the dump.

    Why? The by far smoothest way to copy a database from one SQL Server instance to another is by BACKUP/RESTORE.

    Tools like Copy Database Wizard are more brittle, and they may not copy the database faithfully to 100 per cent - something you are guaranteed with BACKUP/RESTORE.

    I recall that I played quite a bit with CDW when SQL 2005 was in beta and CDW was a new thing. I reported several issues, of which some were addressed, but maybe not all. I have not used it very much since.

    That said, the issue you are running into is trivial once you trawl through that very verbose dump:

    Cannot drop database "FactoryDB" because it is currently in use.

    Before running CDW, run this:

    ALTER DATABASE FactoryDB SET SINGLE_USER WITH ROLLBACK IMMEDIATEDROP DATABASE FactoryDB

    But don't be surprised if CDW fails on some real error like an unsupported data type.

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.