8

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.

Improve this question
1
  • Please see Aarons answer. That is a much more effecient way for your database
    – Ali Razeghi - AWS
    Dec 18, 2012 at 17:05

1 Answer 1

Reset to default
7

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.
Improve this answer
8
  • I have SQL 2012 installed, is it OK if I install SQL 2008 R2 on the same machine? Does order of installation matter?
    – Salman A
    Dec 18, 2012 at 17:15
  • Yes, you can install 2008 R2 on the same machine, but to keep things simple just install the database engine, no client tools and all that. You can uninstall the 2008 R2 instance once you've moved all your 2000 databases off of 2000.
    – Aaron Bertrand
    Dec 18, 2012 at 17:26
  • 1
    I would advise against installing versions that you do not need on production machines, but no, it doesn't interfere but you might get some warnings about shared components. As a general rule though, never install anything on prod that you don't need in prod. It increases security surface area for attacks, introduces bugs, and increases management.
    – Ali Razeghi - AWS
    Dec 18, 2012 at 17:26
  • 1
    @Ali how do you know the machine Salman is talking about is a production machine? Or that it isn't the only machine available?
    – Aaron Bertrand
    Dec 18, 2012 at 17:30
  • That's why I specified 'on prod' instead of just 'don't do it' and answered 'but no it doesn't interfere'. As far as only machine available, you can install these on desktops even and his database is 30GB as stated. Computers with 30GB free storage are pretty common, but again, it was just advice, not a hard 'this is the only answer'. I looked at his blog and he's very dev focused, which means there's a high likeliness he doesn't get involved with ops best practices as much as a ops guy. I think it's good advice, I'm surprised how many people dev in prod when they dont have to.
    – Ali Razeghi - AWS
    Dec 18, 2012 at 17:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.