How to take Transaction Log Backups for All SQL Server Databases in SQL Server

Transaction Log Backups for All SQL Server Databases

As a DBA or developer, it is your responsibility to create the scripts necessary to take a backup of all databases' transaction logs. As you can see from the where clause, I am omitting system databases because database id > 4. Any databases that are in full or bulk recovery mode will have transaction log backups taken by the script.

Scripts to Copy Below

USE MASTER

GO

DECLARE @BackupPath varchar(100)

--Provide the backup path

SET @BackupPath = 'C:\Backup\'

DECLARE @DatabaseName AS varchar(128)

 

DECLARE Cur CURSOR FOR

--Change the select query for the DBs you like to backup

SELECT

  name

FROM sys.databases

WHERE database_id>4

and recovery_model_desc in ('Full','BULK_LOGGED')

 

OPEN Cur

FETCH NEXT FROM Cur INTO @DatabaseName

WHILE @@FETCH_STATUS = 0

BEGIN

  DECLARE @SQL varchar(max) = NULL

  DECLARE @DBNamewithDateTime varchar(128) = NULL

  SET @DBNamewithDateTime = @DatabaseName + '_' + REPLACE(CAST(GETDATE()

       AS date), '-', '') + '_' + REPLACE(CAST(CAST(GETDATE() AS time)

       AS char(8)), ':', '')

 

  SET @SQL = 'BACKUP LOG [' + @DatabaseName + '] TO  DISK = N''' +

              @BackupPath + '' + @DBNamewithDateTime + '.trn''

              WITH NOFORMAT, NOINIT,  NAME = N''' + @DBNamewithDateTime

              + '-Tran Log Backup'',

                 SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10'

 

  PRINT @SQL

  EXEC (@SQL)

  FETCH NEXT FROM Cur INTO @DatabaseName

END

CLOSE Cur

DEALLOCATE Cur