May 16, 2006 at 7:18 am
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
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