Post reply

Evaluate a backup plan

  • June 4, 2004 at 3:25 pm

    #108352

    Hello all,

    I ran across this doc on backups and it looks a bit sketchy to me.  Can a few gurus have a look at this and:

    1-Tell me whats wrong with this plan?

    2-Tell me how it should be changed?

    Thanks in advance for your input!

    -----------------------------

    Weekend Full (With Indexing) – 1200 - Saturday:

    All databases

     

    Daily Differential (No Indexing) – 1200 and 1600 – M-F:

    All databases but not the master and msdb

     

    Transaction Log Backup (With Truncate) – Every 2 Hours – M-F 0800-1600:

    All databases except for master and msdb

    ----------

    EOM – Database Full (Sent off-site for 1 year):

    EOY – Database Full (Sent off-site not to return):


    -Isaiah

  • June 6, 2004 at 12:31 pm

    #509155

    Hi,

    I can suggest one thing to you.

    Use diffrential backup in the morning and Evening only means schdule them for 7:00 and 21:00 daily

    During the day you schedule the Transaction log backup for 30 min ( depends on the how busy the the database is) between 08:00 - 20:00

    This plan will give you maximum 30 min loss of data in case of disaster.

    I hope this all makes some sense to you.

    Deep

  • June 6, 2004 at 2:10 pm

    #509159

    The master and msdb database should be backed up minimum of every day also.  They are so small, I don't know why anyone would not do this. 

    Derrick Leggett
    Mean Old DBA
    When life gives you a lemon, fire the DBA.

  • June 7, 2004 at 9:35 am

    #509272

    thanks for the suggestions.  This is what I was looking for.

    Also,  What about the timing and order of, the full, diff and transaction backups?  What are some standard strategies for implementing these in the real world?

    My guess is Trans first then full every night if its not too big, diff if it is very large.  Any suggestions?


    -Isaiah

  • June 7, 2004 at 4:22 pm

    #509351

    Isaiah,

    The best thing about differential backups is that it reduces the number of backup files to restore if you're taking lots of transaction log backups.

    For example, in my shop we take full backups daily, and transaction log backups every 15 minutes.  That leads to a LOT of transaction log backups.  If we took hourly differential backups, the most backups we would have to restore is 5.  The full, appropriate differential, and up to 3 transaction log backups.  If we didn't take the differentials, we could be faced with restoring the full backup and as many as 96 transaction log backups.  We have a script that creates the transaction log restore statements, so its not an issue, and we don't take differential backups.

    The biggest drawback to differential backups is that they each contain all of the changes that have taken place since the last full backup.  In a very active database, they could grow to be much larger than a full backup. (But if thats the case, you'd probably want to schedule the full backups more frequently.)

    Hope this is some help to you,

    Steve

Viewing 5 posts - 1 through 4 (of 4 total)

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