How do you move a SQL Server 7 database to SQL Server 2012?

Question

I would like to move a database from SQL Server 7.0 to SQL Server 2012. They are not compatible for a backup and restore. Is there a way to export from SQL Server 7.0 and import it to 2012? I only need the tables -- no views or stored procedures.

• msi77: Are detach/attach command not working with adjusting the compatibility level after that?

Answer 1

Solution 1

Copy the data using an ETL process. This assumes you have a SQL Server 7 instance up and running with the database accessible. You have several tools at your disposal, but the simplest approach I could see is:

1. Script out all the SQL Server 7 objects and create them in SQL Server 2012. You might have to adjust the script if there's any SQL Server 7 specific syntax.

2. Use an ETL process to copy data. Some alternatives:

   • BCP - Use BCP to dump every table to a RAW or text file, then use BCP to load them into the SQL Server 2012 objects.
   • Import/Export Wizard in SQL Server 2012 - Use the Import/Export wizard to create data transfer tasks.
   • SSIS - This is essentially the same as the Import/Export Wizard, but with more functionality.

Overall, this could be a tedious process, but allows you to copy data directly from one database to the other. If you go the BCP route, you will need some storage for the dump files, but this doesn't have to be database storage.

Solution 2

Upgrade the database by using an intermediate step of upgrading to SQL Server 2005. This assumes you have access to SQL Server 2005 media and a server to stand it up on. SQL Server 7 can not be upgraded to SQL Server 2008 as that upgrade path is not supported, but if you can restore the database to SQL Server 2005, you can then successively backup the SQL Server 2005 version and restore it to SQL Server 2012.

• mrdenny: I'd highly recommend Solution 2 over Solution 1.
• xpda: That's what I did, except sql 7 > 2000 > 2008 > 2012 because I didn't have 2005 available.
• Mike Walsh: I agree - Option 2 is definitely the best bet - and look at this answer to see some pros and cons on in place vs migrate - especially true with something like this. And congrats for getting off of 7.0! ;-)