2

On my TFS database server all the databases are set to Full Recovery Model. We don't do any log shipping and just do full db backups each night so would there be any impact to TFS is we changed our recovery model to Simple? (the log files are getting really large)

Thank you,

David
  • 321
  • 2
  • 16

2 Answers2

2

Your TFS data is at risk. Your current backup is not sufficient to guarantee a restore of your TFS environment.

As TFS consists of more than one database (at least tfs_configuration and tfs_collection) there can be transactions that span those databases. Since transactions are not final until complete on a single DB you can get corruption. If you restore to a point in time one db could not have finished the transaction and roll back, while the other completes.

This will cause the TFS restore to fail.

https://msdn.microsoft.com/en-us/library/ms253070.aspx

You must follow the manual backup procedure detailed the documentation to be guaranteed a restore.

Or you can use the built in backup tool in the TFS Administration Console.

NOTE: The SQL Databases, their configuration, and TFS configuration is owned by TFS and should not be changed beyond what has been documented by the product team.

MrHinsh - Martin Hinshelwood
  • 393
  • 3
  • 12
  • Thank you. I am using the built in backup tool in the TFS Administration Console. It's scheduled to run every day and keep 8 days of backups then those are backed up on tape providing another 3 weeks of daily backups. Using this backup approach would simple recovery model work? – David Jan 18 '16 at 16:30
  • If you change the way that the backup works in a way other than is documented then you will not be supported. – MrHinsh - Martin Hinshelwood Jan 19 '16 at 07:15
  • Ok I'm confused... Is using the built in backup in the TFS Admin Console not supported??? Or are you trying to say that it won't work if the databases are in simple recovery mode? – David Jan 19 '16 at 14:58
  • If you change the way the backups work outside of that which the tool configures for you then you are no longer supported. Is simple recovery an option? – MrHinsh - Martin Hinshelwood Jan 21 '16 at 01:53
  • I'm not looking to change how the backups are done. I'm just looking to change the recovery model of the databases involved. What I'm asking is will changing the recovery model of the databases break any functionality in TFS or the current way I'm doing backups? The tool didn't necessarily configure the databases that way. By default SQL server creates databases with a full recovery model. I'm just looking to possibly change it to simple recovery model. – David Jan 21 '16 at 15:55
  • David, the SQL Databases for TFS are created, managed, and owned by TFS. You should not be making any changes to the configuration of SQL Server, the databases or TFS beyond what is documented. Since I cant find anything documented on MSDN I would suggest that its NOT supported. – MrHinsh - Martin Hinshelwood Jan 21 '16 at 19:13
  • 1
    "The situation for TFS is slightly more complicated as as it is imported the the TFS databases are in sync and therefore you should use Recovery Model= FULL and schedule transaction log backup." https://social.msdn.microsoft.com/Forums/windowsapps/en-US/95aff2b8-0432-42a7-bf09-f4e5a2204148/tfs-2015-backup-schedule-and-recovery-model?forum=tfsgeneral – MrHinsh - Martin Hinshelwood Jan 21 '16 at 19:17
-1

If you aren't using the logs for recovery/replication and you're taking full nightly backups of the database(s) then yes, the Simple recovery model is safe. The impact is that SQL Server will manage the transaction log(s) for you.

After setting the recovery model to Simple you may or may not need to manually run a shrink operation, IDK. You'll have to see for yourself.

joeqwerty
  • 109,901
  • 6
  • 81
  • 172
  • It might be safe for a single DB system, but it does not guarantee a restore in A system that has transactions that go across DB's. Marked transaction logs are required to take backups when the system is running. You should refer users to the applications documentation. – MrHinsh - Martin Hinshelwood Jan 16 '16 at 01:06
  • 1
    Hmm... Yeah, I was only looking at it from the perspective of individual and unrelated databases. Thanks for adding some clarity. – joeqwerty Jan 16 '16 at 01:12