TFS DB Restore–DB Set to Suspect

When you have configured backups with TFS admin console and restore them in case of a need, you are expecting, your TFS databases to restore without any issues. But you might run into this situation if you just, missed simple thing to check. For example the target server SQL version, should exactly match to the source or your production TFS, SQL version. Let’s have a look at learning experience I went through due to a slight mistake, which taught me how much vigilant you need to be when you are doing the, administrative work with TFS.

This was the problem I was having. tried restoring several times, taking backups, and wasted about a day before I realize where it went wrong. It was restoring Report Server DBs and even SharePoint related DBs of my TFS 2015.2.1 databases.

I used same SQL version, SQL 2012 SP1. So what could be wrong here? Gone though few articles available but could not identify whre this is going wrong.

https://support.managed.com/kb/a398/how-to-repair-a-suspect-database-in-mssql.aspx
http://matijabozicevic.com/blog/sql-server-development/how-to-repair-a-suspect-database-in-sql-server
http://www.sqlservercentral.com/articles/suspect/69133/

I did not want to repair TFS DBs as it is something that could potentially corrupt the TFS DBs, so would not be able to restore the TFS, even if I manage to repair the DB to get them online. Because this repair comes with a data loss which is not possibility considering TFS DBs. TFS DBs need to be in sync for exact point of time to be able to restore a TFS instance.

With this in mind I further investigated the “dbcc checkdb” command in SQL here. With the better understanding of the command I tried below on one of the TFS warehouse DB to check what is wrong, without trying to do a data loss repair. It was required to set the DB to emergency access to execute dbcc checkdb.

EXEC sp_resetstatus Tfs_Warehouse;
ALTER DATABASE Tfs_Warehouse SET EMERGENCY
DBCC checkdb(Tfs_Warehouse)

There was a message as shown below and it was not the version but the edition of the SQL server causing the issue.

Warning: You must recover this database prior to access.
Msg 945, Level 14, State 2, Line 3
Database 'Tfs_Warehouse' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
Msg 909, Level 21, State 1, Line 2
Database 'Tfs_Warehouse' cannot be started in this edition of SQL Server because part or all of object 'DimTestResult' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.
Msg 933, Level 21, State 1, Line 2
Database 'Tfs_Warehouse' cannot be started because some of the database functionality is not available in the current edition of SQL Server.

My production TFS was on SQL 2012 SP1 Enterprise, but I have setup a target machine with SQL 2012 SP1 Standard. Below command in a query widow can give the version and the edition information of SWL server.

SELECT @@VERSION

SELECT SERVERPROPERTY('edition')

Target server

After installing the SQL 2012 SP1 enterprise DB restore was successful. It is important to check the SQL DB server version and the edition, is the lesson learnt.