Post reply

Help in creating Transactional log backups

  • January 4, 2007 at 10:42 am

    #681265

    Easier than trying to do date string manipulation in TSQL is to use the SQLMAINT utility.  You can specify to do TLog backups (-BkUpLog), remove old backups (-DelBkUps), and it will write each backup to separate files, timestamping each file name just as a maint plan would do it.  You do not need to make a maintenance plan, and you don't run into "portability" issues among different users running Enterprise Manager remotely.

    Hope this helps



    Mark

  • January 4, 2007 at 10:43 am

    #681266

    Thanks Tom for the information. I would try it out and would get back.

    Mark -

    can you please brief me as to how to do with SQLMAINT Utility? Thanks.

     

  • January 4, 2007 at 11:33 am

    #681284

    No problem. 

    1. Creating your job to schedule and perform this backup.
    2. Create a jobstep.  Set Type either to

      • TSQL and use "Exec xp_sqlmaint..", or
      • CMDEXEC and use sqlmaint. 

    Parameters are the same in both cases, and are well documented in BOL (look in the index for "sqlmaint").  Here are a couple examples, I've set the parameters close to what I think you want, but reveiw BOL to be sure.

    TSQL:

    EXECUTE master.dbo.xp_sqlmaint N'-D MyDB -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir -DelBkUps 2days -BkExt "TRN"'

    CMDEXEC:

    sqlmaint -D MyDB -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir -DelBkUps 2days -BkExt "TRN"

    Hope this helps



    Mark

  • January 4, 2007 at 11:40 am

    #681286

    Thanks Mark for your reply. i would try it out.

Viewing 4 posts - 16 through 20 (of 20 total)

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