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:

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.

only supported on SQL Server Enterprise Edition

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

TITLE:Microsoft SQL Server Management Studio

------------------------------

Restore of database'Tfs_Tfs01'failed.(Microsoft.SqlServer.Management.RelationalEngineTasks)

Database'Tfs_Tfs01'cannot be started inthisedition of SQL Server because part orall of object'tbl_LocalVersion'isenabled with data compression orvardecimal storage format.Data compression andvardecimal storage format are only supported on SQL Server Enterprise Edition.

Database'Tfs_Tfs01'cannot be started because some of the database functionality isnotavailable inthe current edition of SQL Server.(Microsoft SQL Server,Error:909)

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…

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…

1

Thiscollection has SQL Enterprise features enabled.Ifyou are moving the collection across SQL Server Editions please read the documentation(http://go.microsoft.com/fwlink/?LinkId=166007) to see how this impacts you.

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…

Find all objects with compression enabled

1

2

3

4

5

6

7

8

9

10

11

12

SELECT

SCHEMA_NAME(sys.objects.schema_id)AS[SchemaName]

,OBJECT_NAME(sys.objects.object_id)AS[ObjectName]

,[rows]

,[data_compression_desc]

,[index_id]as[IndexID_on_Table]

FROM sys.partitions

INNER JOIN sys.objects

ON sys.partitions.object_id=sys.objects.object_id

WHERE data_compression>0

ANDSCHEMA_NAME(sys.objects.schema_id)<>'SYS'

ORDER BY SchemaName,ObjectName

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.

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.

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.

Did you know that the DOD has made it illegal to do waterfall? For the first time in many years the Department of Defence (DOD) in the United States had made a major update to its procurement rules. They can no longer be held accountable for holding up our industry, and being culpable for its inability to move towards agility. The last vestiges of the old ways are gone.

To my understanding there is a frustrating misunderstanding of reality when one thinks that the Product Owner can reject a single story at the Sprint Review. This is the fallacy of the rejected backlog item.