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

Posted on

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.

Answer :

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.

Leave a Reply

Your email address will not be published. Required fields are marked *