Backup Transaction Logs Using T-SQL

I am trying to backup my Transaction Logs using T-SQL with the aid of a job. The problem I am running into is that I need it to backup every three hours and retain one days worth of transactional logs. This is the query I got so far.

BACKUP LOG CA001 TO DISK = 'S:\AZTARGP\CA001\ca001_TRN_bkuplog.trn' with retaindays = 1

Any suggestions welcomed.At my place, we have a separate cleanup job that
deletes the files using xp_cmdshell .|||Try the Database Maintenance Plan. It's a database object and one of the properties is "Remove Files Older Than ___". It creates an entry in your SQL SERVER AGENT under JOBS to run the plan as scheduled.

It also allows much more than just simple backups and it very easy to manage.

I set up a Sunday backup that keeps it's prior Sunday file but removes older ones, but that backup doesn't affect my ongoing daily backups and logfile changes (separate jobs).

Also: I notice you're referencing a "S:" drive. Does SQL backup recognize mapped drives? Maybe that's just a shortcoming of the Database Maintenance Plan Object. It would seem risky. I'd rather do it to a separate (physical) volume physically attached, then immediately copy that the mapped drive (that's what I do). That way a RAID corruption on the DB volume doesn't corrupt the backup too. Also; my backup volume is just RAID 1 (mirrored), since that's easily transfered to a different server in a disaster situation.|||The S:\ drive is actually an iscsi drive that is recognised by the server. What is funny is that the maintenance plan for the database backup works just not the transaction logs. I also have checked to make sure the database was not in simple recovery mode.

I am sort of trying to find a way to give my backup names a date and time. Then I want to only keep a days worth of transaction logs becuase we do a full backup of the database everynight.

I do have

declare @.bkupdate datetime
set @.bkupdate = getdate()

but I don't know how to include it into my sql statement. When I do I get an syntax error.

BACKUP LOG CA001 TO DISK = 'S:\aztargp\ca001\CA001_' + @.bkupdate + '_TRNLOG.trn' with retaindays = 1

I am just trying to use t-sql until I can find the problem with why my database maintenance plan is not working. Thanks for the help so far.|||OK. Here's one that works here.

BACKUP DATABASE [wfab] TO DISK = N'd:\SQL Backups\Daily\mydb_db_latest.bak' WITH INIT , NAME = N'mydbLatest', SKIP , FORMAT

This hard codes the filename. I have this running every 6 hours. Surely using a variable for the filename would be fine, although why not build the whole thing first.

So:

declare @.bkdbName varchar(50)
set @.bkdbName = 'mydb' + Convert(varchar(50),getdate(),110)

declare @.bkName varchar(255)
set @.bkName = 'd:'
set @.bkName = trim(@.bkName) + '\SQL Backups\Daily\'
set @.bkName = trim(@.bkName) + trim(@.bkdbName)

BACKUP DATABASE [wfab] TO DISK = trim(@.bkName) WITH INIT , NAME = trim(@.bkName), SKIP , FORMAT

The reason I liked it without the special naming is because then it shows up pre-registered when I want to do a restore. I do a scheduled copy of these backup files to various places before the next backup takes place. Actually; I copy it to a standby server (as with the half-hour log files), ftp it to an offsite server, and I have a scheduled CD burn (encrypted) to my desktop at 3:30 every day, that I take home with me. This in addition to replication - I think it's safe to say we'll never loose much.

I haven't tried the above - I'm curious if it would then cause all the prior backups to show on the pre-registered backup file list. That would be very nice for restores - something we do regularly to test environments.|||code works great. Had to change a couple of things. How can I get the @.bkdbName to also contain the time?|||code works great. Had to change a couple of things. How can I get the @.bkdbName to also contain the time?
Here's (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_3vtw.asp) a link to MSDN for TSQL Ref. Make this a favorite.

Look at "Cast and convert" for conversion formats.

This should help:

select CONVERT(varchar(8),getdate(),114)
Result is: 15:57:34 Note: 114 returns 11 or 12 characters, but you probably don't need miliseconds.

Use SUBSTRING to parse out the ":" since that's probably not allowed as part of a filename.

I'm curious (and too lazy to try it). After creating a couple of these backups, does it list all these backups on the drop-down list when you go into the RESTORE dialog box? I know that works if the name is static.|||Have not tried it yet. Will when I get a change this afternoon.|||Thanks guys for the help. I also found out what was wrong with my DB maintenance plans. When you set to backup master,model,msdb and other databases in one plan it works fine for the db backups but not the translog. When i created a seperate plan for my user databases the transaction log backups completed successfully.