The Problem
- You have a database (e.g. TFS collection database) in SQL Server Enterprise Edition.
- You want to backup and restore this database on a SQL Server Standard Edition.
- You get an error message on restore that some features are not supported.
The Solution
- On your Enterprise Edition Server:
- Backup your database before the next step.
- As described here, you can run a T-SQL query to determine where Enterprise features like data compression is used.
- Jag Padda provides a script that will return T-SQL statements as the result, which you can then run against the database to disable compression.
- Now backup up your database and restore it on SQL Server Standard Edition.
Update (June 9th, 2012): Ed Holloway pointed out that the easiest way to downgrade your TFS database is to simply run this script:
exec prc_EnablePrefixCompression @online = 0, @disable = 1
