Posted By

jprochazka on 11/06/12


Tagged

mssql backup


Versions (?)

Who likes this?

2 people have marked this snippet as a favorite

ringo380
johnw


Microsoft SQL Server: Back up all transaction logs.


 / Published in: SQL
 

Backs up all database transaction logs within a Microsoft SQL Server instance except for databases set to simple recovery mode.

  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  

You need to login to post a comment.