Saturday, February 18, 2012

Moving a SQL Server Database from Enterprise to Standard Edition

Downgrading a SQL Server DB 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