Post reply

Transaction log backup trick

  • April 19, 2005 at 9:02 am

    #113647

    Hi all,

    I would like to backup transaction log every 4 hours to create 6 separate backup files every day (let say, TrLog_12am.trn, …TrLog_8pm.trn) and overwrite them every next day. How to do this trick besides creating 5 separate backup jobs?

    Thanks

  • April 20, 2005 at 2:53 am

    #553695

    Have a look at maintenance plans.....there is an option there to schedule backups, optimisations, etc multiple times a day...ie every 4-5-6 hours etc.

    B.T.W....backup's every 4 hours will produce 6 backups a day!

  • April 20, 2005 at 2:55 am

    #553696

    I agree with Andrew, the maintenance plan option is probably going to be your best bet, although I'm assuming that you only want 5 backups (instead of the 6) so that you can do a full backup each night too?

  • April 20, 2005 at 6:01 am

    #553743

    No Doubt. Maintainence Plan is the best option to take backups, schedule them and delete the older ones.

    I am not sure what kind of env, you have. But if you are working in a OLTP, then it is recommended to have T-log backups every 15-20 mns. and Differential backups may be at a interval of 4-6 hours.

    --Kishore

  • April 20, 2005 at 6:22 am

    #553745

    A little more direct than using the full maintenance plan wizard is to use the sqlmaint utility.  Check the paramaters in BOL.  You can specify all you need, without having the maintenance plan.



    Mark

  • April 20, 2005 at 7:02 am

    #553761

    I had the same problem where I had to backup a prod. DB 4 times a day, and at the end of every day the network guys backed up the entire server (along with my 4 backup files). Then the next day the files were overwritten.

    I created a SP that looked at the time it was ran, and based on an hour range I set up, did a backup to a different physical file (or device if you want to) every time, with the overwrite switch.

    Then I schedule only one job calling that sp every 4 hrs. If the job failes I setted to page me.

    I even got fancier: backed up other DBs twice a day using the same SP, and a bigger DB only once mid day.

    Let me know if you would like the code.

  • April 20, 2005 at 7:37 am

    #553774

    Thank you, guys. But the core of my question was not how to create scheduled log backup (I used maintenance  plan wizard too) but to get 6 log backup files that have names related to the time of backup (e.g. TrLog_12am.trn, etc) during the day and overwrite them next day. Probably, I will go the way that CR proposed- create SP and schedule it.

  • April 20, 2005 at 8:34 am

    #553795

    Another idea is to use a job with Agent Tokens to name your log file by "job start time" for instance.

    Here is an example of what the job text might look like:

    BACKUP LOG [pubs] TO DISK = 'c:\Pubs_Log_Backup_[STRTTM].bak'

    [STRTTM] is a SQL Agent substitution token that will be replaced with the job start time. This will allow you to have 1 job that runs on a scheduled basis and have the same name reused each day so that the file is overwritten.

    Here is a link that list all the job tokens and some good guidelines for using them.

    http://www.sqldev.net/sqlagent/SQLAgentStepTokens.htm

    Cheers,

    Shane

  • April 20, 2005 at 11:01 am

    #553852

    Nice one, Shane.  I had not heard of those tokens before, and had always written code to get those values (like time stamping a backup file).  Just goes to show that even after 10 years of SQL Server experience, there always seems to be something new to learn.

    Thanks again.



    Mark

  • April 20, 2005 at 12:38 pm

    #553875

    Agree, it's cool. Shane, sorry, but when I tryed your syntax got back file with '[STRTDT]' in name, not it's replacement. What I am doing wrong? Thanks 

  • April 22, 2005 at 1:03 am

    #554267

    Great trick. Yuri I suggest you to read article in the link till the end

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

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