blog

Restore TFS backups from SQL Enterprise to SQL Express

Published on
5 minute read

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.

Restore TFS backups from SQL Enterprise 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.

TITLE: Microsoft SQL Server Management Studio
------------------------------

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

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.SmoExtended)

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

Database '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.
Database '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)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=909&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

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…

Restore TFS backups from SQL Enterprise to SQL Express

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…

This 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…

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
AND SCHEMA_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.

Restore TFS backups from SQL Enterprise to 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.

ALTER INDEX ALL ON LinkTreesLatest REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON tbl_AuthorizationObject REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON tbl_Branch REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON tbl_BranchMapping REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON tbl_LocalVersion REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON tbl_nodes REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON tbl_PendingChange REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON tbl_PendingChangeRecursive REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON tbl_PendingMerge REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON tbl_PendingRollback REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON tbl_PropertyValue REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON tbl_RegistryItems REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON tbl_SecurityAccessControlEntry REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON tbl_Version REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON tbl_WorkingFolder REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON tbl_WorkingFolderHistory REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON tbl_WorkspaceMapping REBUILD WITH (DATA_COMPRESSION = None);
GO

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.

Restore TFS backups from SQL Enterprise to SQL Express

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.

code-and-complexity install-and-configuration problems-and-puzzles upgrade-and-maintenance blog 2012-2 backups code configuration database mssqlserver puzzles sql sql-enterprise sql-express sql-server sql-server-error team-project-collection tfs wit

Connect with Martin Hinshelwood

If you've made it this far, it's worth connecting with our principal consultant and coach, Martin Hinshelwood, for a 30-minute 'ask me anything' call.

Our Happy Clients​

We partner with businesses across diverse industries, including finance, insurance, healthcare, pharmaceuticals, technology, engineering, transportation, hospitality, entertainment, legal, government, and military sectors.​

Trayport Logo
Higher Education Statistics Agency Logo
Qualco Logo
MacDonald Humfrey (Automation) Ltd. Logo
Philips Logo
Kongsberg Maritime Logo
ALS Life Sciences Logo
Slicedbread Logo
Boxit Document Solutions Logo
Emerson Process Management Logo
Epic Games Logo
Boeing Logo
Capita Secure Information Solutions Ltd Logo
Bistech Logo
Hubtel Ghana Logo
ProgramUtvikling Logo
Slaughter and May Logo
Microsoft Logo
Nottingham County Council Logo
Department of Work and Pensions (UK) Logo
Royal Air Force Logo
Washington Department of Enterprise Services Logo
Washington Department of Transport Logo
New Hampshire Supreme Court Logo
Akaditi Logo
Jack Links Logo
Workday Logo
Emerson Process Management Logo

NIT A/S

Flowmaster (a Mentor Graphics Company) Logo