Automating Transaction Log Backups for All SQL Server Databases

With the use of T-SQL you can generate your transaction log backups and with the use of cursors you can cursor through all of your databases to back them up one by one. With the use of the DATABASEPROPERTYEX function we can also just address databases that are either in the FULL or BULK_LOGGED recovery model since you cannot issue transaction log backups against databases in the SIMPLE recovery mode.

Here is the script that will allow you to backup the transaction log for each database within your instance of SQL Server that is either in the FULL or BULK_LOGGED recovery model.

You will need to change the @path to the appropriate backup directory and each backup file will take on the name of "DBname_YYYDDMM_HHMMSS.TRN".

DECLARE @name VARCHAR(50) -- database name   DECLARE @path VARCHAR(256) -- path for backup files   DECLARE @fileName VARCHAR(256) -- filename for backup   DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'C:\Backup\'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
   + '_'
   + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')
DECLARE db_cursor CURSOR FOR
SELECT name  FROM master.dbo.sysdatabases  WHERE name NOT IN ('master','model','msdb','tempdb')
   AND DATABASEPROPERTYEX(name, 'Recovery') IN ('FULL','BULK_LOGGED')
OPEN db_cursor    FETCH NEXT FROM db_cursor INTO @name 
WHILE @@FETCH_STATUS = 0    BEGIN 
       SET @fileName = @path + @name + '_' + @fileDate + '.TRN'
       BACKUP LOG @name TO DISK = @fileName

       FETCH NEXT FROM db_cursor INTO @name    END 

CLOSE db_cursor    DEALLOCATE db_cursor