Issue with changing recovery model of DB before taking tran log bkup

  • I am changing the recovery model of the db to Full before taking transaction log backup, sometimes the recovery model does'nt change from Simple to Full resulting in the failure of the tran log backup's on that db, inspite of adding the below code as the first step in the sql job:

    SQL code:

    DECLARE @Mode sql_variant

    SELECT @Mode = DATABASEPROPERTYEX('DBname', 'Recovery');

    IF @Mode = 'SIMPLE'

    ALTER DATABASE DBname

    SET RECOVERY FULL;

    In the tran log backup sql job, I have the above code as step1 and then in step2: tran log backup.

    what can cause the db not to alter to Full? The err in the event log does'nt give much info.

    Note: The reason I am trying to alter the recov model is bcause of one of the application's stored proc runs randomly and changes the recov model to Simple.

    Please advice.

  • If I ever alter the recovery model to Full from Simple the first thing I do is take a full backup, before even thinking of taking a log backup, simply because until the change there would not be any relevant/consistent data in the log, as logging had been switched off by the use of the simple recovery model. So the full backup is required to make your log backups relevant.

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • After you change the recovery to Full, do you take a full backup/differential backup before taking the transaction log backup?

  • Jo Pattyn (3/18/2008)


    After you change the recovery to Full, do you take a full backup/differential backup before taking the transaction log backup?

    After changing the recovery to Full, I take tran log backup. Currently the way it's been set up by our old DBA: Full db backup @night and tran log backup's every 4 hours. I shall try to change to the new way which is:

    every sunday: Full db backup

    Other days: Diff backup's of the database

    Trans log backup's: will be every 30 mins or 1hr

    And also when I am trying to alter the recov model why's it's not changing to Full?? Thanks!!

  • Mh (3/18/2008)


    Jo Pattyn (3/18/2008)


    After you change the recovery to Full, do you take a full backup/differential backup before taking the transaction log backup?

    After changing the recovery to Full, I take tran log backup. Currently the way it's been set up by our old DBA: Full db backup @night and tran log backup's every 4 hours. I shall try to change to the new way which is:

    every sunday: Full db backup

    Other days: Diff backup's of the database

    Trans log backup's: will be every 30 mins or 1hr

    And also when I am trying to alter the recov model why's it's not changing to Full?? Thanks!!

    I'm curious why you need a script to keep switching it to Full? Is something switching it back? Like has been mentioned before - the minute you switch from Full to simple - your log backups are "garbage" since the transaction log chain was broken.

    If you can -find a recovery model and stick with it. Someone else mentioned databases magically switching recovery models on them - something about a monitoring tool "deciding" to change their recovery. In any case - find the culprit and terminate with prejudice. You should be the only one in charge of what the recovery model is.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Carolyn Richardson (3/18/2008)


    If I ever alter the recovery model to Full from Simple the first thing I do is take a full backup, before even thinking of taking a log backup, simply because until the change there would not be any relevant/consistent data in the log, as logging had been switched off by the use of the simple recovery model. So the full backup is required to make your log backups relevant.

    Since in Simple recovery model, all the committed transactions are deleted from the datafile, so it does'nt allow tran log backups to be taken right. In my case since I am changing the recovery model from Simple to Full, why I should take the full db backup first, and then the tran log backup's?? Actually we are already taking full db backup every night. Thanks in advance!!

  • See http://msdn2.microsoft.com/en-us/library/aa173551(SQL.80).aspx

    "Restoring a database using both database and transaction log backups works only if you have an unbroken sequence of transaction log backups after the last database or differential database backup. If a log backup is missing or damaged, you must create a database or differential database backup and start backing up the transaction logs again. Retain the previous transaction logs backups if you want to restore the database to a point in time within those backups."

    Your change to the recovery model has effectively broken the sequence as for the period you were in simple mode meant no logs were being kept.

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • Thanks, I shall take full db backup and then subsequent tran log backup's after changing the recovery model from Simple to Full.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply