backup

  • Dear all

    If db recovery model is simple and I take full back up every day. And if my recovery mdel is full and I take only fulll backup every day.

    (I do not take transaction log back up at all.)

    Will these recovery models give same results or am I missing some thing.

    Note that I donot want to restore to particular point of time. Only want to restore to last back up.

    Lots of info availsble on net but for my situation I feel recovery model does not matter. Correct me if i am wrong.

    Also want to check if for recovery model full if i take only full back up every day will the transaction log keep on growing or it will truncate when full bavkup taken?

    Thanks and regsrds

  • Hi,

    If your recovery model is full and you don't take a log backup then your log file will fill the drive it is sat on. If you don't care about point in time recovery use the simple recovery model.

    Thanks,

    Simon



    MCSE: Data Platform
    MCSE: Business Intelligence
    Follow me on Twitter: @WazzTheBadger
    LinkedIn Profile: Simon Osborne

  • Thanks for quick reply. How do I test this. I need to show the diff that log will not get trucated even if we take full back every day. Can u tell so.e was to demonstrate the difference between a imple and full recovery model

  • Krishna1 (6/19/2013)


    Thanks for quick reply. How do I test this. I need to show the diff that log will not get trucated even if we take full back every day. Can u tell so.e was to demonstrate the difference between a imple and full recovery model

    It is easy to test this

    Follow the below steps:

    1. Set the recovery model as FULL

    2. Create a table in the database

    3. Insert lots of data in the table and check the log size

    4. Take a FULL backup

    5. Repeat steps 3 & 4 as many times as you want

    Note down the log size after every iteration and you will find that the log size keeps on increasing.


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Krishna1 (6/19/2013)


    How do I test this. I need to show the diff that log will not get trucated even if we take full back every day.

    Create a database, set it to full recovery. Schedule full backups. Run data modifications (create, populate and drop a table if you have nothing else). Wait for the full drive error.

    Please read through this: http://www.sqlservercentral.com/articles/Administration/64582/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • If db recovery model is simple and I take full back up every day. And if my recovery mdel is full and I take only fulll backup every day.

    (I do not take transaction log back up at all.)

    Will these recovery models give same results or am I missing some thing.

    ---- Taking Full Backup on the database which is in either Full or Simple or Bulk-logged recovery model will give the same result only and there won't be any difference.

    Thanks

    Bala

  • balasaukri (6/19/2013)


    If db recovery model is simple and I take full back up every day. And if my recovery mdel is full and I take only fulll backup every day.

    (I do not take transaction log back up at all.)

    Will these recovery models give same results or am I missing some thing.

    ---- Taking Full Backup on the database which is in either Full or Simple or Bulk-logged recovery model will give the same result only and there won't be any difference.

    Thanks

    Bala

    Sorry, but that's wrong. The recovery model setting has no influence on when and whether the log files get "cleared". Just follow Gail's link a little further up for an explanation as to why.

    --------------------------------------------------------------------------
    A little knowledge is a dangerous thing (Alexander Pope)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

  • Aplogies if I didn't mean what I actually mean.

    I mean to say the content (data) of backup file will be same. If a full backup is taken on a database with any type recovery model - the content (data) in the backup file would be the same.

    And ofcourse Full backup will make certain effect on the Logfile - It won't mark log file for deletion (truncation) if a db in Full or Bulk Recovery model. It will take certain portion of Log file for sychronization.

    Only Log backup will mark the log file for deletion (truncation) after the log backup except two situations - (i) When it is being using used with conjuction of NO_TRUNCATE or COPY_ONLY. (ii) When the log backup completes while Full/ Differential backup is still running. As the Full/ Differential backup requires certain portion of log backup it wont allow log backup to truncate.

  • Dear All

    Thanks for your replies. I have done testing of .ldf file size using 2 newly created DB. One DB is simple recovery model and other Full recovery model.

    Created following table in both DBs

    create table a ( a1 char(1000) default 'a1',

    a2 char(1000) default 'a2',

    a3 char(1000) default 'a3',

    a4 char(1000) default 'a4',

    a5 char(1000) default 'a5',

    a6 char(1000) default 'a6',

    a7 char(1000) default 'a7',

    iinteger)

    and used

    insert into a (i) values (1)

    go 20000

    Please refer to the pdf file of my findings. From the PDF file i noticed that Full recovery model after transaction log back up us taken it resused the log space. But in the Simple recovery model its keeps on growing, which i know is not correct.

    Let me know what is that i am doing wrong

    Regards

    Krishna1

  • Please post the output

    select name, recovery_model_desc, log_reuse_wait_desc from sys.databases where name = 'your db name'

    dbcc sqlperf(logspace) [for the two databases]

  • pls find the screen shot in the attachment

  • First Screen shot is not clear - another image is on that. Moreover the log_reuse_wait_desc for t1 (if it is SIMPLE) looks as LOG_Backup... It can't be. How you created this db (t1) really don't know. Please create another db with SIMPLE and repeat the tasks as you mentioned. It should work. No way for the misbehaviour.

  • sorry did not realise. I have corrected the screen shot in this attachment.

    I created t1 using ssms. and it shows the recovery model as simple

  • (i) Displaying Log_reuse_wait_desc as Log_Backup for a db in SIMPLE contradicts.

    (ii) Is there any active transaction pending on this db?

    (iii) Change the Recovery to Full.... and then again to SIMPLE then try.

    (vi) Have you tried to create a new db in SIMPLE and do the same exercise?

  • That means you just switched to simple and a checkpoint hasn't occurred. The wait types don't change instantly, run a checkpoint and the Log Backup will disappear.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 15 (of 24 total)

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