Backing Up All Microsoft SQL Server Transaction Logs

Published November 20th, 2012


I found this query a while back when I had an issue with transaction logs tied to Microsoft SQL Server databases growing to the point where the space they were taking up became a problem. This query works great for reclaiming large chunks of hard drive space especially when backing up to a drive not holding your databases.

First off this query will back up transaction logs only for databases with their recovery mode set to FULL or BULK_LOGGED. It will also skip system databases as well. You can add other databases which you do want the transaction log backed up to this list by modifying and adding them after ‘tempdb’ in the following line.

WHERE name NOT IN ('master','model','msdb','tempdb') AND DATABASEPROPERTYEX(name, 'Recovery') IN ('FULL','BULK_LOGGED')

You can also set the directory in which to place the backups by modifying the following line.

SET @path = 'C:\Backup\'

The full SQL query is as follows.

--
-- Backup transaction logs for all databases except the system databases.
-- Backup files will be saved in the format DBname_YYYDDMM_HHMMSS.trn.
--
 
DECLARE @name VARCHAR(50) -- Database name.
DECLARE @path VARCHAR(256) -- Path to backup folder.
DECLARE @fileName VARCHAR(256) -- Filename for backup.
DECLARE @fileDate VARCHAR(20) -- Used for file name.
 
-- Specify database backup directory.
SET @path = 'C:\Backup\'
 
-- Specify filename format.
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