Restore TFS backups from SQL Enterprise to SQL Express

You can get an error when trying to restore TFS backups that certain features are only supported on SQL Server Enterprise Edition.

If you try to restore a SQL Server database that you backed up from an Enterprise version of SQL Server (and that includes Developer Edition) you may encounter an error when trying to restore that database to another SQL Server that is Standard or Express edition.

  • Update I got an email from Grant Holiday with a little titbit of information.

    Instead of running a bunch of ALTER INDEX commands, you can just follow the instructions at http://support.microsoft.com/kb/2712111, which is what the error message refers to. Essentially, run this command in each of the TFS Configuration & Collection databases:

    Grant Holiday

Error restoring databases that uses compression to SQL Express
Figure: Error restoring databases that uses compression to SQL Express

This is due to features that are provided in the Enterprise edition of SQL that are not present in anything lower. What sometimes gets folks confused is that Developer Edition has feature parity with Enterprise.

In this case some of the objects (tables & indices) have compression enabled and compression is only available in SQL Enterprise and Developer Editions.

You also can’t say that you were not warned as when you detached the collection from your old TFS server you ignored the warning that resulted in the very message above. How do I know that you did? Coz I did as well…

image
Figure: This collection has SQL Enterprise features enabled

When you detach a collection you will get a warning if it is using enterprise features. I had always gotten into the habit of ignoring this as I had never encountered any issue. Now I have…

Now that we know what the problem is we need to take steps to remove the compression that is enabled on the objects within our collection. When you create a collection with the enterprise features enabled TFS enabled the compression automatically so we will always need to down-level our databases if we encounter this issue. But first we need to find the objects…

This SQL statement allows you to find all of the index objects that are currently enabled for compression. Just because it is enabled does not mean that it is in use, but just having it enabled will disallow your ability to import your database into SQL Standard or SQL Express.

List of objects that have compression enabled in SQL Enterprise
Figure: List of objects that have compression enabled in SQL Enterprise

We can then use this list to alter the objects and remove the compression. To do this we need to rebuild the indices without compression enabled as it is not just an on/off flag.

If you have a large amount of data than this can and will take some time. Or considerably longer!

For me, my collection was less than 100mb so the entire script ran in milliseconds. On hundreds of gigabyte’s I would  expect this to take a very long time.

SQL backup restore is now successful
Figure: SQL backup restore is now successful

Woot.. now that I have removed that enterprise only feature SQL Express now no longer chokes on the restore.

Conclusion

Although the enterprise features are useful at scale they can get in the way when you are tinkering or if your instance is just that small. If your TFS instance is small enough to go into SQL Express I would recommend using http://tfs.visualstudio.com instead as you will always have the latest features and someone else maintains your server.