3

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:

  1. Acquire database over internet from customer
  2. Restore SQL 7 database directly into MSDE 2000 (on a dedicated XP machine)
  3. Raise database compatibility to MSDE 2000
  4. Backup database to a new file
  5. Restore MSDE 2000 database directly into SQL Server 2008 R2
  6. Raise database compatibility to SQL Server 2008
  7. Backup database to a new file
  8. Restore SQL 2008 R2 database directly into SQL Server 2014
  9. Raise database compatibility to SQL Server 2014
  10. Run all scripts on database for software changes over time
  11. Backup database to a new file
  12. 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 want to even need to acquire their database, and many of them don't want us to see their data.

Is there a method which I can easily distribute onto our client's machine, as part of the installer, which can automatically perform the upgrade from any given database version from SQL 7 to SQL Server 2014?

EDIT

To be a bit more clear on the installation process, this is an installer we already have capable of restoring databases directly from 2000 to 2008 R2. But we need to expand this installer to accommodate for upgrading databases ranging from MSSQL 7 to 2014. As I now understand, I can jump straight from 7 > 2005 > 2014. But that's still a hop I would like to eliminate, if possible. I would hate for our installer to load both 2005 and 2014 on our clients' machines.

Improve this question
13
  • 2
    Have you considered exporting the data, creating a new empty database, and then importing? That way, you wouldn't need to bother with the intermediate SQL and Windows versions.
    – Dan Guzman
    Commented Nov 2, 2016 at 0:23
  • FWIW, steps 3 and 6 will happen automatically when you get to step 8. Under the covers, the databases are all being upgraded anyway, but just run in an older compat level. My point is this: if you could somehow go from v7 to 2005 in one step, you could simply attach those files to a 2014+ instance, and it will automatically upgrade. That might be worth investigating.
    – Randolph West
    Commented Nov 2, 2016 at 0:29
  • I haven't tried this, but you should be able to restore to 2005, which will bring compat level to 80 (2000), then backup and restore that database to 2014, which will bring compat level to 100 (2008). But I agree with Dan - seems like you should just restore the database once and then use one of the many ways to migrate a database, including import/export, 3rd party tools like SQL Compare, etc.
    – Aaron Bertrand
    Commented Nov 2, 2016 at 0:29
  • 2
    This documentation says that you can detach a database from 7.0 and attach it to 2005, but I would try a backup/restore first, as that's a little safer. All that said, no, there is no one-step magic wizard to move a database from 7.0 to 2014, unless you do it manually using something like bcp.
    – Aaron Bertrand
    Commented Nov 2, 2016 at 0:41
  • @RandolphWest Actually no it doesn't happen automatically. I've always had to explicitly go into the database options and raise it manually. There's actually a few times I've restored one in 2008 R2, backed it up without changing that, and tried to restore in 2014, only to find out it fails because I never raised its compatibility.
    – Jerry Dodge
    Commented Nov 2, 2016 at 0:50

2 Answers 2

Reset to default
2

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 !

Improve this answer
1
  • I wouldn't really call the "two versions" limitation a myth. It's just a change in support. It used to be quite true that SQL Server would only restore a backup from the current version or two prior versions. They have simply been able to drop the limitation since SQL Server backups haven't changed enough since SQL Server 2005 to necessitate breaking backward compatibility.
    – Bacon Bits
    Commented May 17, 2021 at 13:10
1

That makes your life easier, negating the triple hop from 7-2000-2008-2014.

Still not a great solution however. I would invest the time in writing a PowerShell script to automate this process.

Improve this answer

Your Answer

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

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