Link to home
Start Free TrialLog in
Avatar of Ali Shah
Ali ShahFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Help in Copy Database Wizard From One SQL Server to Other SQL Server

Hi Guys,

I am trying to copy the database from Azure VM to Rackspace VM From SQL Server 2012 to SQL Server 2014. However i am getting the error. I have attached the file containing all my steps and the error.

Can you please help?CopyDBError.docx

regards,
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

The error said to check the Event Log (try the Event Viewer) and not the SQL Server Log.
Anyway you marked an information line and not an error.
Hi,

Your Job is failed. Check the event log and provide us the error details. !

https://msdn.microsoft.com/en-us/library/ms188664.aspx

Hope it helps!
Avatar of Ali Shah

ASKER

Hi Vitor,

Thanks for redirecting me to the right direction.
There are five errors and 2 warnings as you can see below
User generated imageI will start from the bottom most Distributed Com

Distributed Com Error
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID 
{806835AE-FD04-4870-A1E8-D65535358293}
 and APPID 
{EE4171E6-C37E-4D04-AF4C-8617BC7D4914}
 to the user NT SERVICE\SQLSERVERAGENT SID (S-1-5-80-344959196-2060754871-2302487193-2804545603-1466107430) from address LocalHost (Using LRPC) running in the application container Unavailable SID (Unavailable). This security permission can be modified using the Component Services administrative tool.

Open in new window


SQL Package120 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-->Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
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 asyncWrite)
   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()
InnerException-->The wait operation timed out
 Operator: NT Service\SQLSERVERAGENT
 Source Name: CFDEVCABFIND_Cloudapp_net,65534_CFTESTAPPS01_Transfer Objects Task
 Source ID: {327716D3-5E37-4142-A1F8-8A3249692DB4}
 Execution ID: {9D4ACBB0-4038-49DE-A7AE-A8F5A2B29CBD}
 Start Time: 12/21/2016 11:21:19 AM
 End Time: 12/21/2016 11:21:19 AM

Open in new window


SQLPackage120Error
  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-->Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
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 asyncWrite)
   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()
InnerException-->The wait operation timed out
 Operator: NT Service\SQLSERVERAGENT
 Source Name: CDW_CFDEVCABFINDSQL_CFTESTAPPS01_5
 Source ID: {8E0A5934-F497-4441-8960-0818FED68C81}
 Execution ID: {9D4ACBB0-4038-49DE-A7AE-A8F5A2B29CBD}
 Start Time: 12/21/2016 11:21:19 AM
 End Time: 12/21/2016 11:21:19 AM
 Data Code: 0

Open in new window


SQLPackage120 Error
 Event Name: OnTaskFailed
 Message: 
 Operator: NT Service\SQLSERVERAGENT
 Source Name: CFDEVCABFIND_Cloudapp_net,65534_CFTESTAPPS01_Transfer Objects Task
 Source ID: {327716D3-5E37-4142-A1F8-8A3249692DB4}
 Execution ID: {9D4ACBB0-4038-49DE-A7AE-A8F5A2B29CBD}
 Start Time: 12/21/2016 11:21:19 AM
 End Time: 12/21/2016 11:21:19 AM

Open in new window


SQL Package120 Warning
  Event Name: OnWarning
 Message: 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.

 Operator: NT Service\SQLSERVERAGENT
 Source Name: CDW_CFDEVCABFINDSQL_CFTESTAPPS01_5
 Source ID: {8E0A5934-F497-4441-8960-0818FED68C81}
 Execution ID: {9D4ACBB0-4038-49DE-A7AE-A8F5A2B29CBD}
 Start Time: 12/21/2016 11:21:19 AM
 End Time: 12/21/2016 11:21:19 AM

Open in new window


SQL Package120 Error
Package "CDW_CFDEVCABFINDSQL_CFTESTAPPS01_5" failed.

Open in new window


SQL Server Agent Warning
SQL Server Scheduled Job 'CDW_CFDEVCABFINDSQL_CFTESTAPPS01_5' (0x092C5132F9EBCC4393793649742FECAA) - Status: Failed - Invoked on: 2016-12-21 11:20:34 - Message: The job failed.  The Job was invoked by User SpacesUser.  The last step to run was step 1 (CDW_CFDEVCABFINDSQL_CFTESTAPPS01_5_Step).

Open in new window

The error now looks more logical.
The SQL Server agent account from the destination server (where the job is created) doesn't have permissions in the source server and that makes sense since you're using a local virtual service account (NT Service\SQLSERVERAGENT).

Instead of going for a SMO method, can't you go for the Dettach/Attach method?
I've wrote this article explaining how to use that method.
Thanks i have tried detach and attach method as well as described in your article.

The file share i have given is as

\\ServerName\E$\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA

Open in new window


This time i get the different error.

Event Name: OnError
 Message: Failed to connect to server CFDEVCABFINDSQL.
StackTrace:    at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
   at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.OpenConnection(Server& server, ServerProperty serverProp)
InnerException-->A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
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)
   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, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 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()
InnerException-->The network path was not found
 Operator: NT Service\SQLSERVERAGENT
 Source Name: CFDEVCABFINDSQL_134_213_216_188,1433_Transfer Objects Task
 Source ID: {9FAFD9DF-948F-4ECC-84A3-B9031C34D921}
 Execution ID: {3D1BA8FE-0911-40CF-B0F2-EEA9A7765F38}
 Start Time: 12/21/2016 12:11:42 PM
 End Time: 12/21/2016 12:11:42 PM

Open in new window


It seems its complaining about the source server not being accessible. However i can remotely connect to the server via any SSMS or even RDP it
InnerException-->The network path was not found
Check if you can access the network path: 1:\\ServerName\E$\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA

This has to be permission issue: "Message: Failed to connect to server CFDEVCABFINDSQL."

Btw, can you access the source server and check for a SQL Agent job called CDW_TargetServer_SourceServer and check the history execution for that job?
You can also post here a copy of that job log.
Yes i was not able to open the folder from the target server. It says windows cannot access the folder.

Regarding the agent job on the source server, there is no such job CDW_TargetServer_SourceServer where target server is my servername and source is definitely the source servername.

However one thing i have just confirmed that the source server (Azure) and the destination server (Rackspace) are totally on different network. Can this be the issue?
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks i will ask my Network admin to solve the access issues.
I have already tried the detach / attach and backup and restore.
The issues i am getting with backup and restore is that i can't copy the database and get Unspecified Error. google tells that if file is over 30 GB then window throws this error.
shah36, any feedback about this issue?