Sql-server – How to copy all Database from Source to Destination server along with login credentials

sql server

My scenario is like this:

Task to accomplish: Migrate all Source server databases approx 100 to Destination server along with sql login credentials.

I tried with copy database wizard. It only worked at my local instances.
When I did same task into my network server then following error raised:
Date 11/26/2018 11:47:16 AM
Log Job History (CDW_DB-TESTSRV_SAMEER-DBA_SQL2017_2)

Step ID 1
Server SAMEER-DBA\SQL2017
Job Name CDW_DB-TESTSRV_SAMEER-DBA_SQL2017_2
Step Name CDW_DB-TESTSRV_SAMEER-DBA_SQL2017_2_Step
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: SAMEER-DBA\Administrator. Microsoft (R) SQL Server Execute Package Utility Version 14.0.3045.24 for 64-bit Copyright (C) 2017 Microsoft. All rights reserved. Started: 11:47:17 AM Error: 2018-11-26 11:47:17.51 Code: 0x00000000 Source: DB-TESTSRV_SAMEER-DBA_SQL2017_Transfer Objects Task Description: Failed to connect to server DB-TESTSRV. StackTrace: at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect() at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.OpenConnection(Server& server, ServerProperty serverProp) InnerException–>Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. StackTrace: at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity) at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect() End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:47:17 AM Finished: 11:47:17 AM Elapsed: 0.484 seconds. The package execution failed. The step failed.

Thanks in advance.

Best Answer

You can probably achieve your goal with a few lines of Powershell code using the cmdlets provided by dbatools, which is a free PowerShell module with over 400 SQL Server best practice, administration, development and migration commands included.

Migrating application databases with dbatools provides useful examples for the tasks you want to do.

Step 1 – Check for connections

Get-DbaProcess -SqlInstance SourceServer -Database MigratingDatabase | Stop-DbaProcess

Step 2 – Migrate the database(s)

Copy-DbaDatabase -Source SourceServer -Destination DestinationServer -Database MigratingDatabase -BackupRestore -NetworkShare \\fileshare\

Step 3 – Migrate the user logins

Copy-DbaLogin -Source SourceServer -Destination DestinationServer -Login AppReadOnly, AppReadWrite, DOMAIN\AppUser