Restore a SQL 7 database into SQL 2014 without in-between versions?

Our software is over 20 years old, and dates way back to databases such as Sybase and SQL 7. Now let's ignore the Sybase part. We have many old customers using very old versions of our software, varying between SQL 7 (2001-2002) to SQL 2014 (We support today). Technically speaking, our software can still actually run on MSDE 2000. But that's besides the point. We enforce all updated clients to use at least SQL Server 2008 R2, but officially support up to 2014. And yes, we do have many old customers still using ancient versions of our software on Windows '95.

Anyway, down to the question. Currently, whenever we have one of these old customers return and wish to upgrade, we have to go through a big elaborate upgrade process. Now the scripts are the easy part. I am actually just finalizing the latest major upgrade script, which supports upgrading databases from our 2001 (SQL 7) version to our latest version. The very same script accommodates for any in-between version.

However, before I can run these scripts, I first have to take the database through a series of manual and tedious steps. I have it sped up to this process:

• Acquire database over internet from customer

• Restore SQL 7 database directly into MSDE 2000 (on a dedicated XP machine)

• Raise database compatibility to MSDE 2000

• Backup database to a new file

• Restore MSDE 2000 database directly into SQL Server 2008 R2

• Raise database compatibility to SQL Server 2008

• Backup database to a new file

• Restore SQL 2008 R2 database directly into SQL Server 2014

• Raise database compatibility to SQL Server 2014

• Run all scripts on database for software changes over time

• Backup database to a new file

• After installing software for customer, restore database over internet

So as you can imagine, a lot of returning customers would be a full-time job. I suppose I could build some sort of automation on this XP machine, but this is still a manual process I'm looking to eliminate. We don't w

If I was in your place, I would go the below route which is not as tedious as you would think of.

• Backup your sql 7.0 database and restore it on sql server 2005.

• Backup your sql server 2005 database and restore on sql server 2014.

Its a myth that you cannot upgrade a database to a version more that is more then two versions newer.

I have written an elaborated answer about the pre and post migration steps that will be a handy reference to you.

For automation, you can use dbatools (powershell based) - start-sqlmigration that supports migration from sql server 2000 to 2016 (newest version out).

Note: You have to test out a lot of stuff as this is a major leap for your application !

StackExchange Database Administrators Q#153956, answer score: 2