Microsoft SQL Server 2005/2008:Backup Transaction Logs Script.

Use this script to take backup of transaction logs.

1.Create [Transaction_Logs_backup] Stored Procedure.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE Procedure [dbo].[Transaction_Logs_backup]
as
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(500) -- path for backup files
DECLARE @fileName VARCHAR(500) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
DECLARE @Sql varchar(500)-- Script to take backup of transaction

SET @path = '\\192.168.0.242\Database Backup\192.168.0.110\Transaction_logs\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),105)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name  not IN ('master','model','msdb','tempdb')

OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @name 

WHILE @@FETCH_STATUS = 0 



BEGIN 
       SET @fileName = @path +'Transaction_log_'+ @name + '_' + @fileDate + '.trn'

       SET @Sql='USE [' + @name + '] BACKUP LOG '+@name+' TO DISK ='''+@fileName +''''
       print 'Backup Started...'+@name       
       print '   '
       EXEC(@Sql)
       print @Sql
       print '   '       
       print 'Backup Completed...'+@name       
       print '   '
       FETCH NEXT FROM db_cursor INTO @name 
END 

CLOSE db_cursor 
DEALLOCATE db_cursor

2.Execute this procedure.

EXEC [Transaction_Logs_backup]

Check Messages....

Backup Started...1
  
Processed 112 pages for database '1', file '1_log' on file 1.
BACKUP LOG successfully processed 112 pages in 0.071 seconds (12.908 MB/sec).
USE [BTS] BACKUP LOG BTS TO DISK ='C:\1.trn'
  
Backup Completed...1