My last post showed a little script that I use for taking full backups of all my databases on an Instance.

This script below will take a transaction log backup of all your database in full recovery mode, again it can be used in conjunction with the SQL agent to schedule it regularly. This is one size fits all to transaction log backups though and this may not fit all environments perfectly. If you have a database that needs a different more specific schedule from transaction log backups on the instance in question then you will need to find a way to work that in.

DECLARE @DBName varchar(255)
DECLARE @DS VARCHAR(50)
DECLARE @Path VARCHAR(255)



DECLARE TLOG_BACKUP CURSOR FOR

select name from sys.databases
where recovery_model = 1 --Only return databases in full recovery 
AND name NOT IN ('TEMPDB') -- Exclude TEMPDB
AND state = 0 --Exclude offline databases, they won't backup if they offline anyway
AND Source_database_id is null -- Removes snapshots from the databases returned

OPEN TLOG_BACKUP


FETCH NEXT FROM TLOG_BACKUP
INTO @DBName

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
--Set the filename values of the transaction log backup files
SET @DS = REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '') + '_'
    + REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', '')
SET @Path = 'C:\DATA\Backup\'
SET @Path = @path + @DBNAME + '_'+ 'LOG'+'_' + @DS + '.trn'
--Take the backup

BACKUP LOG @DBNAME 
TO DISK = @Path 
WITH
    FORMAT, INIT,  SKIP, NOREWIND,
    NOUNLOAD, STATS = 10

FETCH NEXT FROM TLOG_BACKUP
INTO @DBName


END

CLOSE TLOG_BACKUP
DEALLOCATE TLOG_BACKUP
GO