Post reply

Schedule a transaction log backup via T-SQL

  • May 4, 2004 at 5:09 am

    #107654

    Hi!

    I'm developing a script where I first backup the database and then while the script is executing periodically backup the transaction log to record all the transactions taking place while also continuously clearing out the transaction logs to minimise space usage. I'd like to schedule the transaction log backups to occur hourly. Is there any way I can do this within my T-SQL script?

    Any help in this regard would be greatly appreciated.

     

  • May 4, 2004 at 1:08 pm

    #505182

    If you are using transactions within your script you can simply use the "With Mark" portion of the begin transaction call. This will mark your transaction log for you. You can then roll back your database to each of those marked transactions if needed.

    DECLARE

    @strMark nvarchar(128)

    ,

    @strComment nvarchar(128)

    BEGIN TRAN @strMark WITH MARK @strComment

    Please note the @strMark can not have any spaces however @strComment can. 

     

    Barring that you can setup a job to do the log backup and have it run every hour.

    BACKUP LOG <DBName> TO <Log Backup Device>




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • May 5, 2004 at 12:06 am

    #505244

    Thank you very much.

    You've helped tremendously!

     

Viewing 3 posts - 1 through 3 (of 3 total)

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