Moving a SQL Server Database from Enterprise to Standard Edition

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