Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
msdb Recovery model Simple? RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    I am configuring my MOPS system (Proj Server 2007 on Win2008 and SQL2008) for disaster recovery using transaction log marking. Wouldn't I want my msdb system database in Full recovery model rather than Simple recovery model? MS Documentation states this contains the backup/recovery history and log marking history for the databases. Yet it recommends Simple. If my log marking is occuring 4x a day, and this database is only backed up at night, without Full recovery model wouldn't I lose the ability to restore to the last transaction log? I appreciate your help as I am out of my element here!
    Tuesday, March 1, 2011 10:22 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi, You are correct, if you have the database in simple recovery, your roll back position will only be the nightly backups, meaning if you have a failure during the day, then you will lose any changes between the last backup and the time of failure. If you have a full recovery, SQL will maintain a set of transaction logs which allow you to recover to point of failure. However this will generate a large number of files and of course will require a large amount of disk, especially if those databases are transaction heavy. The type of recovery model comes down to your diaster recovery requirements. Hope this helps,
    Alex Burton
    www.epmsource.com | Twitter
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page
    Wednesday, March 30, 2011 8:10 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    http://msdn.microsoft.com/en-us/library/ms187014.aspx

    Relevant only for SQL Server databases that are using the full or bulk-logged recovery models. Go through the article.

    A typical scenario for using marked transactions includes the following steps:

    1. Create a full or differential database backup of each of the related databases.

    2. Mark a transaction block in all the databases.

    3. Back up the transaction log for all the databases.

    4. Restore database backups WITH NORECOVERY.

    5. Restore logs WITH STOPATMARK.

    Cheers. Happy troubleshooting !!! Sriram E - MSFT Enterprise Project Management
    Wednesday, March 2, 2011 12:47 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thank you Sriram, That is not quite the question I am asking. The article actually prompted my question because it states:

  • After a marked transaction commits, a row is inserted in the logmarkhistory table in msdb.

    And the recovery documentation states:

    1. Identify the most recent marked transaction that is available in all of the transaction log backups. This information is stored in the logmarkhistory table in the msdb database on each server.

    So my question is this: If I have set the msdb database to simple recovery (like Microsoft says to do in http://go.microsoft.com/fwlink/?LinkID=144371 at page 24), and my hardware crashes, because the simple recovery model of this database has no incremental data (logs or differential backups), don't I lose all marked transactions of the day and the ability to restore to most recent marks? In simple recovery model, this database is only backed up at night with the full backup and all markings during the day would be lost. Shouldn't this be set to FULL recovery so all mark history is backed up with the logs?

    Thank you for helping!

Wednesday, March 2, 2011 4:41 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi, You are correct, if you have the database in simple recovery, your roll back position will only be the nightly backups, meaning if you have a failure during the day, then you will lose any changes between the last backup and the time of failure. If you have a full recovery, SQL will maintain a set of transaction logs which allow you to recover to point of failure. However this will generate a large number of files and of course will require a large amount of disk, especially if those databases are transaction heavy. The type of recovery model comes down to your diaster recovery requirements. Hope this helps,
    Alex Burton
    www.epmsource.com | Twitter
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page
    Wednesday, March 30, 2011 8:10 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    There is also a good document released on technet yesterday outlining disaster recovery options including database recovery modes at http://technet.microsoft.com/en-us/library/cc952749.aspx

     

    Alex Burton
    www.epmsource.com | Twitter
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page
    Friday, April 1, 2011 8:00 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thank you Alexander. To overcome the loss of the transaction marking data, I left the database in Simple recovery mode and simply take a full backup of the system databases after the logs are marked. That works fine I think.
    Tuesday, June 14, 2011 10:20 PM