Introduction
While the user is trying to transfer the database from SQL Server 2008 Enterprise Edition to the Standard Edition or Express Edition the user may get the following error:
Restore Failed For Server ‘’, (MicrosoÃt.SqlServer.SmoExtended)
Additional information:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer ,Connectionlnlo)
Database ‘ 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 ‘’ cannot be started because some of the database functionality is not available in the current edition of SQL Server. (Microsoft SQL Server, Error: 909) Reasons For SQL Server 2008 Error 909
The reasons for error code 909 are that the SQL Server Enterprise Edition supports Data Compression and varDecimalStorage format Compression. They are not supported in SQL Server standard Edition and Express Edition. So any database created with SQL Server Enterprise Edition that has Data Compression and varDecimalStorage format Compression feature the database cannot be moved directly to any lower edition of SQL Server. We can also not restore the backup of the database which has compressed objects in SQL 2008 standard edition the compression is only supported by the Enterprise Edition only.
How to Fix SQL Server Error Code 909
To fix Error 909 first find and remove the object that has Compression enabled on the database.
- Run The Query To Find Out The Compression In The Database
- After the user has executed the Compression query the next step is to find out the varDecimalStorage of an object. If its zero it means it doesn’t have the Compression.
- Third, execute the query to rebuild the indexes which has Data Compression with option ‘none’ so that Compression can be disabled.
In this procedure the SQL query is run in order to find out the compression in the database and will list all the objects which has data compression.
The query is given below:
The query to find the varDecimalStorage is given below:
The query to rebuild the indexes is given below:
After all the queries are executed re-execute the entire queries to find out if there is any compression left in the database or not.
After all the Compression removing techniques are completed. The user can backup the database from SQL Server Enterprise and restore it on SQL Server Standard or Express Edition.
Solve SQL Server 2008 Error 909 In TFS
To solve error code 909 in TFS (Team Foundation Server) here are the following steps:
- Run the following query in each and every TFS database to find out whether their objects have Compression enabled:
- If the output displays the objects, the next step is to disable the compression on the objects and their respective indexes. Execute the following command with TFS DB’s.sql.
Conclusion
The SQL Server error 909 is the most common problem when the user tries to migrate from SQL Server Enterprise Edition to Standard or Express Edition of SQL Server. It’s because of the compression is enabled in the SQL Server 2008 which causes this error. We also discussed about how to solve this error by executing various queries in the database and TFS database. After all the queries are performed the user can backup the database from Enterprise Edition to any Standard/Express Edition of the SQL Server.
EmoticonEmoticon