question

Torin avatar image
Torin 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?
copy-database
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Just curious, why doesn't it work in 2012? I restore databases in that same fashion all the time in SQL 2012 without any issues.
0 Likes 0 ·
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?
0 Likes 0 ·
Torin avatar image
Torin answered
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!!!
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
Torin avatar image
Torin answered
"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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.