Results 1 to 5 of 5

Thread: transaction log backups... a few questions.....

  1. #1
    Join Date
    May 2003
    Posts
    35

    transaction log backups... a few questions.....

    Hi,

    I am doing a daily full backup of all the databases.I want to backup the transaction log with a tsql like this...


    backup log xyz_db to
    disk ='k:\sqllogbkups\xyz_db.trn'
    with noinit,no_truncate


    I understand the once xyz_db.trn is saved on tape... I have to run a job with the options "with init,truncate only"

    Is this correct ?
    or should I follow some other way of backing up transaction logs....?


    Thanks,
    Copernicus..
    Last edited by copernicus; 06-17-2003 at 02:28 PM.

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Not necessary to use no_truncate, you like to truncate log to reduce log size. I use 'with init' to do first log backup after full db backup to create new log backup file, then use 'with noinit' to backup log afterwards before next db full backup.

  3. #3
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Backing up a transaction log automatically truncates inactive portion of the log, so you don't need second backup with truncate_only option.

    In fact if you do that your earlier backup can't be used to recover database.

  4. #4
    Join Date
    May 2003
    Posts
    35
    Hi,

    So I put this job to run every
    30 mts in production and say at not run this at 2.00 AM ,so that I can put the xyz_db.trn on tape.

    Is this a correct methodology ?

    backup log xyz_db to
    disk ='k:\sqllogbkups\xyz_db.trn'
    with noinit

    Regards,
    Copernicus.

  5. #5
    Join Date
    Sep 2002
    Posts
    5,938
    If you do db full backup at 1am, then you backup log with following at 1:30am:

    backup log xyz_db to
    disk ='k:\sqllogbkups\xyz_db.trn'
    with init

    and backup log with following from 2am to 12:30am next day:

    backup log xyz_db to
    disk ='k:\sqllogbkups\xyz_db.trn'
    with noinit

    You can backup k:\sqllogbkups\xyz_db.trn to tape any time after 12:30am and before 1:30am.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •