Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Please see Aarons answer. That is a much more effecient way for your database –  Ali Razeghi Dec 18 '12 at 17:05
add comment

1 Answer 1

up vote 6 down vote accepted

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.
share|improve this answer
    
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 '12 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 '12 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 Dec 18 '12 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 '12 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 Dec 18 '12 at 17:41
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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