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