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:

  1EXEC [dbo].[prc_EnablePrefixCompression] @online = 0, @disable = 1
  

  - Grant Holiday

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.

 1TITLE: Microsoft SQL Server Management Studio
 2------------------------------
 3
 4Restore of database 'Tfs_Tfs01' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)
 5
 6------------------------------
 7ADDITIONAL INFORMATION:
 8
 9An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.SmoExtended)
10
11------------------------------
12
13Database 'Tfs_Tfs01' cannot be started in this edition of SQL Server because part or all of object 'tbl_LocalVersion' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.
14Database 'Tfs_Tfs01' cannot be started because some of the database functionality is not available in the current edition of SQL Server. (Microsoft SQL Server, Error: 909)
15
16For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=909&LinkId=20476
17
18------------------------------
19BUTTONS:
20
21OK
22------------------------------

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…

1This collection has SQL Enterprise features enabled. If you 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…

 1SELECT
 2SCHEMA_NAME(sys.objects.schema_id) AS [SchemaName]
 3,OBJECT_NAME(sys.objects.object_id) AS [ObjectName]
 4,[rows]
 5,[data_compression_desc]
 6,[index_id] as [IndexID_on_Table]
 7FROM sys.partitions
 8INNER JOIN sys.objects
 9ON sys.partitions.object_id = sys.objects.object_id
10WHERE data_compression > 0
11AND SCHEMA_NAME(sys.objects.schema_id) <> 'SYS'
12ORDER 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.

 1ALTER INDEX ALL ON LinkTreesLatest REBUILD WITH (DATA_COMPRESSION = None);
 2ALTER INDEX ALL ON tbl_AuthorizationObject REBUILD WITH (DATA_COMPRESSION = None);
 3ALTER INDEX ALL ON tbl_Branch REBUILD WITH (DATA_COMPRESSION = None);
 4ALTER INDEX ALL ON tbl_BranchMapping REBUILD WITH (DATA_COMPRESSION = None);
 5ALTER INDEX ALL ON tbl_LocalVersion REBUILD WITH (DATA_COMPRESSION = None);
 6ALTER INDEX ALL ON tbl_nodes REBUILD WITH (DATA_COMPRESSION = None);
 7ALTER INDEX ALL ON tbl_PendingChange REBUILD WITH (DATA_COMPRESSION = None);
 8ALTER INDEX ALL ON tbl_PendingChangeRecursive REBUILD WITH (DATA_COMPRESSION = None);
 9ALTER INDEX ALL ON tbl_PendingMerge REBUILD WITH (DATA_COMPRESSION = None);
10ALTER INDEX ALL ON tbl_PendingRollback REBUILD WITH (DATA_COMPRESSION = None);
11ALTER INDEX ALL ON tbl_PropertyValue REBUILD WITH (DATA_COMPRESSION = None);
12ALTER INDEX ALL ON tbl_RegistryItems REBUILD WITH (DATA_COMPRESSION = None);
13ALTER INDEX ALL ON tbl_SecurityAccessControlEntry REBUILD WITH (DATA_COMPRESSION = None);
14ALTER INDEX ALL ON tbl_Version REBUILD WITH (DATA_COMPRESSION = None);
15ALTER INDEX ALL ON tbl_WorkingFolder REBUILD WITH (DATA_COMPRESSION = None);
16ALTER INDEX ALL ON tbl_WorkingFolderHistory REBUILD WITH (DATA_COMPRESSION = None);
17ALTER INDEX ALL ON tbl_WorkspaceMapping REBUILD WITH (DATA_COMPRESSION = None);
18GO

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.