Sql-server – Timeout expired error while using copy database wizard

restoresql serversql-server-2012ssis

Microsoft made me believe that SQL Server 2012 Copy Database Wizard is the most optimal way of copying a SQL Server 2000 database to SQL Server 2012. After struggling a few hours, I was able to get past some issues and I was able to import small to medium size SQL Server 2000 databases into SQL Server 2012.

However, the wizard constantly fails for a 30GB database with these errors:

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)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject
stateObj)

I have double checked, it is not a filepath, permission or disk space issue. I think the CREATE DATABASE step takes 2 minutes or so and the wizard assumes that the operation timed out. I manually created an empty database with same filepath and filesize using SQL, that worked. Interestingly, a 1GB database copy failed with same errors and succeeded on 2nd try.

Please help.

Best Answer

A much more efficient path than messing with that wizard would be to:

  1. Take a full backup of your 2000 database.
  2. Restore it to a SQL 2005, 2008 or 2008 R2 instance, all of which support 2000 databases. You can still get the evaluation edition of 2008 R2 here, if you don't have any applicable instances in place. Note that Express won't work as it has a 4GB / 10GB data file limitation depending on version.
  3. Change the compatibility level to something above 80 (90 or 100, depending on whether you used 2005 or 2008/2008 R2) using ALTER DATABASE.
  4. Take a full backup of this database after it is no longer in 80 compatibility mode.
  5. Restore this new backup to your SQL Server 2012 instance.
  6. Change the compatibility level to 110 and update statistics.
  7. TEST.
Related Question