Ali Shah
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,
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,
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!
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!
ASKER
Hi Vitor,
Thanks for redirecting me to the right direction.
There are five errors and 2 warnings as you can see below
I will start from the bottom most Distributed Com
Distributed Com Error
SQL Package120 Error
SQLPackage120Error
SQLPackage120 Error
SQL Package120 Warning
SQL Package120 Error
SQL Server Agent Warning
Thanks for redirecting me to the right direction.
There are five errors and 2 warnings as you can see below
I 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.
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
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
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
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
SQL Package120 Error
Package "CDW_CFDEVCABFINDSQL_CFTESTAPPS01_5" failed.
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).
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.
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.
ASKER
Thanks i have tried detach and attach method as well as described in your article.
The file share i have given is as
This time i get the different error.
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
The file share i have given is as
\\ServerName\E$\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA
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
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 foundCheck if you can access the network path: 1:\\ServerName\E$\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER
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_SourceSer
You can also post here a copy of that job log.
ASKER
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_SourceSer ver 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?
Regarding the agent job on the source server, there is no such job CDW_TargetServer_SourceSer
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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?
Anyway you marked an information line and not an error.