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

Scenario:

You are working as DBA or developer and you have to prepare the scripts that should be able to take the transaction log backup for all the databases. You can use these script on ad hoc basis if you need or you can use it SQL Server agent job on schedule. 

The list of databases can be change as per requirement. You can change Select statement and filter for the database you want or don't want to contribute in Transaction Log backup.

I am excluding system databases as you can see that I have database_id > 4 in where clause. The script will take transaction log backup for all the database which are in full or bulk recovery mode.

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



I executed above script and it took transaction log backup with date-time as shown below.
How to take transaction log backup of all databases on SQL Server Instance - SQL Server Tutorial

No comments:

Post a Comment