Post reply

xp_sqlmaint

  • May 16, 2006 at 7:18 am

    #113441

    I need help to write Transaction Log BACKUP JOB with the help of xp_sqlmaint. i try to gather require information, but some how it is not working. i will appreciate if some one can help me to put all together.

    I am trying to do is to create transaction log backup job which runs every hour & delete any 2 or 3 days older Transaction log so to maintain log size smaller.Any help will be highly appreciated.

  • May 17, 2006 at 6:07 am

    #638383

    Seems to me you should just use the Enterprise Manager Maintenance Plan Wizard to create this.  Just have it create a Transaction log backup only- specifying the schedule and backup retention that you specified.. all the options should be there that you want it sounds like..

    I just did it quickly and this was the resulting job it created:

    EXECUTE master.dbo.xp_sqlmaint N'-PlanID 624F1B62-D8FA-4094-8B8C-A6E7BEE45AA6 -WriteHistory  -VrfyBackup -BkUpMedia DISK -BkUpLog  -UseDefDir  -DelBkUps 2DAYS -BkExt "TRN"'

    If you didn't want to do this for some reason then I am sure you could just modify this to not use the planID and instead do something like:

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

    This assumes it will connect with windows authentication to run.. otherwise you can also include the -U sql_user -P sql_user_password (probably right after the -S servername specification)..

    So all this above would be the job step and then you would add the schedule you want directly in the job.. but I would still just use the Maintenance Plan Wizard..

    Good luck.

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

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