locked
Change databases to simple recovery mode RRS feed

  • Question

  • Hi,

    We are using Symantec BackupExec to perform daily backups of our TFS 2010 databases. Currently they are in full recovery mode but the log files increase very rapidly. We perform full backups every night.

    I was wondering if we can change from full to simple recovery mode (is that supported for TFS 2010) and if there are any drawbacks. I know that we will not be able to do a recovery to a specific point in time but it is not necessary.

    Thanks in advance!
    Lars

    Friday, September 23, 2011 9:08 AM

Answers

  • Hi Lars,

     

    Thanks for your post.

     

    For how to use Symantec BackupExec to backup TFS 2010, I suggest you should to contact Symantec BackupExec experts for the better response.

     

    To backup TFS 2010 daily, you can install TFS Power Tools, then create TFS Backup plan in TFS Admin Console to backup TFS DBs daily. For how to use TFS Power Tools to create backup plan, please refer to Bharry’s blog: http://blogs.msdn.com/b/bharry/archive/2010/08/18/backing-up-and-restoring-your-tfs-server.aspx and http://blogs.msdn.com/b/bharry/archive/2010/08/19/backing-up-your-tfs-server-with-sharepoint-and-reporting.aspx.

     

    What’s the detailed mean about “simple recovery mode”?


    John Qiao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, September 26, 2011 3:29 AM
    Moderator
  • Hi Lars and John,

     

    I did some research on TFS Power Tools Backups feature, and found that TFS Power Tools set Recover Full on TFS Databases when create the Backup Plan, it’s hard code in TFS Power Tools Backup Plan feature, we can’t change it to the Simple mode.

     

    For change databases to simple recovery mode when backup using TFS Power Tools, I suggest you submit it as a suggestion to UserVoice site at:

    http://visualstudio.uservoice.com/forums/121579-visual-studio. Microsoft engineers will evaluate them seriously.

     

    In the Backup Plan completely log, you will find this(Although I had changed Tfs_DefaultCollection DB to Simple mode in SQL Server Management Studio):

    [Info   @05:02:48.313] ALTER DATABASE [Tfs_Configuration] SET RECOVERY FULL

    [Info   @05:02:48.631] Successfully prepared database Microsoft.TeamFoundation.PowerTools.Admin.Helpers.Database

    [Info   @05:02:48.631] ALTER DATABASE [Tfs_DefaultCollection] SET RECOVERY FULL

    [Info   @05:02:48.813] Successfully prepared database Microsoft.TeamFoundation.PowerTools.Admin.Helpers.Database

    … … …

     


    John Qiao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, September 28, 2011 8:41 AM
    Moderator

All replies

  • Good question....I have the same setup.  I'd like to change my db's to simple recovery too.  I would think it would be fine but I'll wait for an "expert" answer.
    Friday, September 23, 2011 1:17 PM
  • Hi Lars,

     

    Thanks for your post.

     

    For how to use Symantec BackupExec to backup TFS 2010, I suggest you should to contact Symantec BackupExec experts for the better response.

     

    To backup TFS 2010 daily, you can install TFS Power Tools, then create TFS Backup plan in TFS Admin Console to backup TFS DBs daily. For how to use TFS Power Tools to create backup plan, please refer to Bharry’s blog: http://blogs.msdn.com/b/bharry/archive/2010/08/18/backing-up-and-restoring-your-tfs-server.aspx and http://blogs.msdn.com/b/bharry/archive/2010/08/19/backing-up-your-tfs-server-with-sharepoint-and-reporting.aspx.

     

    What’s the detailed mean about “simple recovery mode”?


    John Qiao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, September 26, 2011 3:29 AM
    Moderator
  • I don't think the question was how to back up.  The question was: CAN YOU CHANGE TO SIMPLE RECOVERY MODE without any ill effects.

     

    Simple Recovery is a SQL setting that doesn't maintain huge transaction log files.

    Monday, September 26, 2011 1:14 PM
  • Hi JohnSLG,

     

    Thanks for your reply.

     

    For the Transaction log file issue, please refer to this article: http://mikefourie.wordpress.com/2010/09/21/minimize-your-tfs-backup-size/.


    John Qiao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Proposed as answer by JohnDMP Tuesday, September 27, 2011 12:14 PM
    • Unproposed as answer by JohnDMP Tuesday, September 27, 2011 12:14 PM
    Tuesday, September 27, 2011 7:47 AM
    Moderator
  • That talks about zapping the log files, which is a nice work around, but it should be possible to put SQL in simple recovery mode and generate no log files.  I do that with other SQL databases not related to TFS that don't require point in time recovery.  I just want to know if that is acceptable.

    Right now our Verson Control log files are at 16 GB.


    This blog says it is possible....I was trying to get a Microsoft response.

    http://msmvps.com/blogs/vstsblog/archive/2009/08/09/stop-sql-server-transaction-log-ldf-files-from-growing-indefinitely.aspx

    • Edited by JohnDMP Tuesday, September 27, 2011 12:34 PM
    Tuesday, September 27, 2011 12:16 PM
  • Hi,

    I am in the same position as John and what we are wondering is if a simple recovery mode (state) on the TFS databases is supported by the TFS 2010 platform.

    Thanks in advance!
    Best regards
    Lars

    Tuesday, September 27, 2011 1:50 PM
  • I just did it.....it's pretty easy...but then I've used SQL Server for years....follow the blog I linked.....my log file went from 16GB to 100KB.  No problems with version control in TFS.

    I'm still in TFS 2005 on SQL Server 2005...in the process of moving.  There is no reason it shouldn't work in 2010/2008.  I changed the Tfs_Configuration and Tfs_DefaultCollection db's on the 2010/2008 box with no error messages....haven't tested TFS with changes yet.

    Tuesday, September 27, 2011 2:27 PM
  • Hi Lars and John,

     

    I did some research on TFS Power Tools Backups feature, and found that TFS Power Tools set Recover Full on TFS Databases when create the Backup Plan, it’s hard code in TFS Power Tools Backup Plan feature, we can’t change it to the Simple mode.

     

    For change databases to simple recovery mode when backup using TFS Power Tools, I suggest you submit it as a suggestion to UserVoice site at:

    http://visualstudio.uservoice.com/forums/121579-visual-studio. Microsoft engineers will evaluate them seriously.

     

    In the Backup Plan completely log, you will find this(Although I had changed Tfs_DefaultCollection DB to Simple mode in SQL Server Management Studio):

    [Info   @05:02:48.313] ALTER DATABASE [Tfs_Configuration] SET RECOVERY FULL

    [Info   @05:02:48.631] Successfully prepared database Microsoft.TeamFoundation.PowerTools.Admin.Helpers.Database

    [Info   @05:02:48.631] ALTER DATABASE [Tfs_DefaultCollection] SET RECOVERY FULL

    [Info   @05:02:48.813] Successfully prepared database Microsoft.TeamFoundation.PowerTools.Admin.Helpers.Database

    … … …

     


    John Qiao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, September 28, 2011 8:41 AM
    Moderator
  • OK....I'm not using that backup plan so I'm good.

    Thanks.

     

    Wednesday, September 28, 2011 11:09 AM