Advanced Search

Microsoft SQL Server: Back up all transaction logs.

/ Published in: SQL
Save to your folder(s)
Backs up all database transaction logs within a Microsoft SQL Server instance except for databases set to simple recovery mode.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. --
  2. -- Backup transaction logs for all databases except the system databases.
  3. -- Backup files will be saved in the format DBname_YYYDDMM_HHMMSS.trn.
  4. --
  5.  
  6. DECLARE @name VARCHAR(50) -- Database name.
  7. DECLARE @path VARCHAR(256) -- Path to backup folder.
  8. DECLARE @fileName VARCHAR(256) -- Filename for backup.
  9. DECLARE @fileDate VARCHAR(20) -- Used for file name.
  10.  
  11. -- Specify database backup directory.
  12. SET @path = 'C:\Backup\'
  13.  
  14. -- Specify filename format.
  15. SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')
  16.  
  17.  
  18. DECLARE db_cursor CURSOR FOR
  19. SELECT name
  20. FROM master.dbo.sysdatabases
  21. WHERE name NOT IN ('master','model','msdb','tempdb') AND DATABASEPROPERTYEX(name, 'Recovery') IN ('FULL','BULK_LOGGED')
  22.  
  23. OPEN db_cursor
  24. FETCH NEXT FROM db_cursor INTO @name
  25.  
  26. WHILE @@FETCH_STATUS = 0
  27. BEGIN
  28.        SET @fileName = @path + @name + '_' + @fileDate + '.trn'
  29.        BACKUP LOG @name TO DISK = @fileName
  30.  
  31.        FETCH NEXT FROM db_cursor INTO @name
  32. END
  33.  
  34. CLOSE db_cursor
  35. DEALLOCATE db_cursor

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.