SQL Clarifications

  • Just want to clarify some points thanks in advance

    1. Simple Recovery model - Ignores transaction logs?.. and is an auto truncate recovery model.

    2.Simple shifting to Full recovery (or vice versa) doesn't affect the database at all.

    3. The auto shrink option of the database shrinks only the transaction log(ldf) and not the data(mdf)

     

    if No. 1 is true (auto truncate) is there a percent in which the tlog is truncated? example when tlog is 80% thats the time it will truncate it.

    What is the best way (time/schedule) of backups using full,differential and tlog. example weekly(1) full backup and hourly diff and tlog...etc...

    "-=Still Learning=-"

    Lester Policarpio

  • Hi Lester,

    1: Does not ignore it. It uses it in case the server goes down, so the database can be recovered if it goes down unexpectedly. But this means that not all the log is required for this, parts of the log that are not required are recycled. You cannot make a log backup. In Simple the log is automatically truncated.

    2: well, it does affect the recovery granurality. In Simple you can only restore a full and differential backups, in full, you can use log backups as well. Note that when you change to full recovery mode, it is not in effect until you take a full backup.

    3: No, it shrinks both.

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • Hi Lester,

    concerning the best way to schedule the backups, it depends on your requirements (whether you can live with loosing one hour/one day worth of data, ho fast do you need it to be restored, how much time the backup takes, ...

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • oopsss wrong way of quoting any ways thanks for the fast reply

     

    "-=Still Learning=-"

    Lester Policarpio

  • So for the full recovery to take effect (after shifting from simple to full) I need to make a full backup?

    "-=Still Learning=-"

    Lester Policarpio

  • Another thing sir about full,diff and tlog is there a detailed site about this? I mean a detailed steps for every choices?

    example:

    1. FOr fast recovery - <this steps>

    2. FOr full recovery(doesnt loose evern 1 hour) - <this steps>

    etc..  

    "-=Still Learning=-"

    Lester Policarpio

  • If i choose to optimize the backups of my databases meaning i want to assure that i cant always make a point in time recovery what could be the best flow of the full, diff and tlog? can you site your own experiece about this?

    "-=Still Learning=-"

    Lester Policarpio

  • I assume you meant  " ... that i CAN always ... "

    There's no "best" answer.  I do a full backup every night of my 280 G database (takes 1/2 hour), and I backup transaction logs every 15 minutes or so during the business day. You can use differentials if you need to, but I don't. Differentials are a convenience during a restore because you don't need to restore all the trans logs since the last full backup, just the transaction logs since the last differential.

  • Thanks for the correction homebrew01

    "-=Still Learning=-"

    Lester Policarpio

  • Lester Policarpio (9/18/2007)


    So for the fullrecovery to take effect (after shifting from simple to full) I need to make a full backup?

    Hi Lester,

    this is correct. If you switch from simple to full, SQL Server will still recycle the transaction log, and will not allow you to back up the transaction log until you take a full backup. It is intelligent enough to realize that if you have no full backup, the transaction log backup makes no sense (you cannot restore it without a full backup). So after switching to full recovery mode do take a full backup.

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • Lester Policarpio (9/18/2007)


    Another thing sir about full,diff and tlog is there a detailed site about this? I mean a detailed steps for every choices?

    example:

    1. FOr fast recovery - <this steps>

    2. FOr full recovery(doesnt loose evern1 hour) - <this steps>

    etc..

    A pretty good place is BOL (Books On Line)

    Search for 'Selecting a Recovery Model' and go from there.

    It will tell you the basics around each option, to get you started.

    /Kenneth

Viewing 11 posts - 1 through 10 (of 10 total)

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