asked
SS2012 - Copy database fails
in 20008 R2 we used to simply restore a backup to make a test database. If needed we would modify the file names and logical names. Since we upgraded to 2012 that doesn't work the same way. I thought rather than addressing that I would use the Copy Database Wizard. But it errors out during the copying data step. I set the package to log to a file, here is the pertinent section: OnInformation,AL-AIR,NT AUTHORITY\SYSTEM,CDW_AL-AIR_AL-AIR_17,{18374F55-C9B2-4B3A-AE5B-60F65A8A32FE},{4AB2392B-FEA2-4944-B441-DBF919C8EC0A},11/22/2016 2:26:34 PM,11/22/2016 2:26:34 PM,0,0x,Transferring data to database VRPE-Winston-Test from VRPE-Winston OnError,AL-AIR,NT AUTHORITY\SYSTEM,AL-AIR_AL-AIR_Transfer Objects Task,{2F56F715-4A76-46E9-AC97-41A0CC66EC89},{4AB2392B-FEA2-4944-B441-DBF919C8EC0A},11/22/2016 2:28:09 PM,11/22/2016 2:28:09 PM,0,0x,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. StackTrace: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) at System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync() at System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket() at System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer() at System.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte& value) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest) at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest) at System.Data.SqlClient.SqlInternalTransaction.Rollback() at System.Data.SqlClient.SqlTransaction.Rollback() at Microsoft.SqlServer.Management.Smo.Transfer.TransferData() InnerException-->The wait operation timed out I can't see why it is failing. What am I missing?
Exactly as John said - backup restore should still work fine. As far as this wizard - It looks like the wizard is failing (timing out) when it starts to begin the bulk copy phase of the SSIS package it has created. Have you ensured that your source and destination Sql Servers can communicate with each other and are ping-able from each other?
As to the restore approach: I finally realized that the GUI appears to be changing the file names back to the original file names when changing forms (not confirmed yet). By clicking OK while on the Files form, it successfully restored and I was able to change the logical names via TSQL. I should just use TSQL for the whole thing. As to the Copy database approach: both databases are on the same server, same instance. I checked on the permissions for the account and even added a few (such as Create Database) - no change. thanks!!!
What do you mean by changing forms? If you don't specify a different physical file name for the restore it will attempt to over-write the existing database file. I don't ever use (ok, hardly) the GUI for a restore operation. I prefer to script it out so I know exactly what it's doing. In regards to the SSIS process, is the source database being used? Is it possible it's a timeout issue due to locking? I *think* by default the process will use "serializable" as it's isolation level so if it can't get a lock in time it could time out. I think.
"changing forms" : the GUI presents a list of forms to choose: General - Files - Options if I recall correctly. I mean changing from the Files form back to the General form. Again - I am not certain this is where the problem happened - it just worked when I clicked OK to start the restore while on the Files form after changing the files to reflect the new DB name. Previously, I had changed the file names, then switched back to the General form to ensure the destination DB was still set to the new name. I'll try it again later to see if that is the issue. Might be fixed in one of the SP releases, if it is an issue. In the SSIS process, yes, the source db is in use. I selected the option that specifies that the source db remains online. But i suppose it could be waiting to lock at a level that requires sole use. I could try it again when I can have exclusive use of the db.
19 People are following this question.
