Post reply

question about full backups and recovery models

  • June 8, 2005 at 8:20 am

    #110246

    I asked a question previously about the HP Data Protector backups.  I've realized that my real question is more generic.

    Some databases in each instance are specified Full recovery model and some are specified Simple recovery model.  All instances are on Windows servers.  Our LAN group takes a full backup of each instance and a separate backup of all related filesystems on a nightly basis.  No transaction log backups have ever been made.

    I'm aware that without transaction log backups we cannot restore to a point in time.

    I need to know if there will be a problem restoring the last full backup to each instance when the Recovery model is mixed Simple and Full.  The database owner for most of the databases designated as Simple Recovery model, mainly the databases such as msdb, master, etc,  is sa.  For the named databases, the owner is something like Administrator and the Recovery model is Full.

    I would appreciate any advice.

  • June 8, 2005 at 1:24 pm

    #563948

    I'll try again to clarify this question.

    Can a Full backup be used to restore databases with the Simple Recovery model as well as the Full Recovery model even though no transaction log backups are made?  This recognizes the fact that all transactions since the last full backup would be lost.

    Thanks -

  • June 8, 2005 at 1:54 pm

    #563961

    Merrily

      Yes your full backups (dumps) are good to restore the database to the point in time when the database dump completed.

      Yes you are correct.  If you never take log dumps you will not be able to do a forward recovery to prevent the loss of data.  Generally for you critical databases you should be taking a log dump at least every hour during the "production" day.

        Rick Phillips

        

     

     

  • June 9, 2005 at 6:16 pm

    #564445

    Beware that your transaction logs on the databases in the full recovery model will grow quite a bit as they won't be truncated.

  • June 10, 2005 at 7:49 am

    #564594

    Ian,

    Thank you.  I am aware of that.  I think that I can remove all logs from before the last full backup.  Is that correct? 

    I'm coming into this after the fact and now realize that the people who originally set things up knew no more than me.  Any advice would be welcome.

    Should I set all of the Recovery models to Simple since there is a full backup Mon through Fri and no transaction log backups?

    I'm trying to get better requirements from the users.    

    Thanks again -

    Merrily Blagen

  • June 10, 2005 at 10:06 am

    #564670

    Merrily,

    As I suggested in your previous thread, if there's no need for point-in-time recovery, change the recovery model to Simple.  You can't actually "remove" the logs, but they'll be truncated when a checkpoint occurs and when a full database backup executes.

    Greg

    Greg

  • June 10, 2005 at 12:03 pm

    #564725

    Thank you again.  I agree with that.

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

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