Feb 19

Written by: Javier Callico
2/19/2011 

The Problem

I got the following error when trying to restore an old database backup into my local 2008 SQLEXPRESS instance:

According to the SQL Server 2008 documentation:

The database being restored must be at least version 80 (SQL Server 2000) to restore to SQL Server 2008 R2. SQL Server 2000 or SQL Server 2005 databases that have a compatibility level less than 80 will be set to compatibility 80 when restored.

The tedious workaround

Luckily SQL Server 2005 is able to restore backups taken with version 7. A workaround is to install it as a new instance, restore the old backup file into an empty database, backup this database and restore it into SQL 2008.

SQL Server 2005 Express can be downloaded from here.

The installation process is straightforward, only two steps are worth mentioning:

1- When selecting the instance name and since SQLEXPRESS is already in use by SQL 2008 something else needs to be selected, I chose SQLEXPRESS2005 but anything else will also do the job.

2- If Windows Authentication was selected (default mode) then check the option to add your windows user to the SQL Administrator Role since this option is not checked by default. I missed it the first time and wasn’t able to do much since only had access to the “public” role.

SQL Management Studio 2008 is able to connect to SQL 2005 instances so there is no need to install the Management Studio 2005.

Now that we are able to connect to both instances…

… we simply restore the SQL 7.0 backup into SQL 2005, take a backup of the same database and restore it in 2008.

After the database was restored into 2008 I uninstalled the SQL 2005 instance and was done.

This solution worked but installing an extra instance of SQL just for restoring a database didn’t seem to me like the easier way.

Again this is a very rare situation but I’ll update this article if I find an easier way.