Migrating TFS from SQL Server Enterprise to Standard can cause problems due to compression

  • Article

When migrating a Team Foundation Server from SQL Server Enterprise to Standard , you might run into this error:

Restore Failed For Server ‘<Servername>’, (Microsoít.SqlServer.SmoExtended)
Additional information:
An exception occurred while executing a Transact-SQL statement or batch.

(Microsoft.SqlServer ,Connectionlnlo)
Database ‘<TFS Database name> cannot be started in this edition of SQL Server because part or all of object tbl_Branch’ 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 Database name>’ cannot be started because some of the database functionality is not available in the current edition of SQL Server. (Microsoft SQL Server, Error: 909)

The error message seems obvious enough, but the question is, how exactly do you proceed? For example, one of the things you would need to find out is which objects have compression enabled on them(yeah, TFS enables compression on some objects in its databases) , and how to get rid of it, so the migration can proceed. Here are the steps:

  1. Run the following query in each TFS database to determine whether there are objects which have compression enabled:

    select so.name,so.type,so.type_desc,sp.data_compression,sp.data_compression_desc from sys.partitions sp
    inner join sys.objects so
    on (so.object_id=sp.object_id)
    where sp.data_compression!=0

  2. If there are objects listed in the output of the query, then the next step is to disable the compression on the objects and their indexes. I actually ended up writing a small script for this(see attachment “Disable Compression on TFS DB’s.sql”). As always, this script does not come with any guarantees. Please do test it thoroughly before running on your production environment. You will need to run this script in the context of each of the TFS databases.

After this, you should be good to proceed with the migration. If you face any issues when trying to disable the compression, please do not hesitate to call Microsoft for support.

Hope this helps. Do let me know if you have any feedback, suggestions or comments. Thanks.

Disable Compression on TFS DB's.sql