solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips

































Top SQL Server Tools






















Automating Transaction Log Backups for All SQL Server Databases

MSSQLTips author Greg Robidoux By:   |   Read Comments (3)   |   Related Tips: 1 | 2 | 3 | 4 | More > Backup

Problem
Maintenance plans are a great thing, but sometimes the end results are not what you expect.  The whole idea behind maintenance plans is to simplify repetitive maintenance tasks without you having to write any additional code.  For the most part maintenance plans work without a problem, but every once in awhile things do not go as planned. Two of the biggest uses of maintenance plans are issuing full backups and transaction log backups.  What other approaches are there to issue transaction log backups for all databases without using a maintenance plan? 

Solution
With the use of T-SQL you can generate your transaction log backups and with the use of cursors you can cursor through all of your databases to back them up one by one. With the use of the DATABASEPROPERTYEX function we can also just address databases that are either in the FULL or BULK_LOGGED recovery model since you can not issue transaction log backups against databases in the SIMPLE recovery mode.

Here is the script that will allow you to backup the transaction log for each database within your instance of SQL Server that is either in the FULL or BULK_LOGGED recovery model.

You will need to change the @path to the appropriate backup directory and each backup file will take on the name of "DBname_YYYDDMM_HHMMSS.TRN".

DECLARE @name VARCHAR(50-- database name  
DECLARE @path VARCHAR(256-- path for backup files  
DECLARE @fileName VARCHAR(256-- filename for backup  
DECLARE @fileDate VARCHAR(20-- used for file name 

SET @path 'C:\Backup\'  

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 

In this script we are bypassing the system databases, but these could easily be included as well. You could also change this into a stored procedure and pass in a database name or if left NULL it backups all databases. Any way you choose to use it, this script gives you the starting point to simply backup all of your databases.

Next Steps

  • Add this script to your toolbox
  • Modify this script and make it a stored procedure to include one or many parameters
  • Create a scheduled task to backup your transaction logs on a set schedule
  • Take a look at this tip that does FULL backups for all databases.
  • Send your improved script to tips@mssqltips.com and we will post it on the site for others to use


Last Update: 8/28/2007


About the author
MSSQLTips author Greg Robidoux
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips


print tip Print  
Become a paid author




Recommended For You








Comments and Feedback:
Thursday, February 05, 2009 - 10:23:11 AM - Netchem Read The Tip

This script offers a big advantage over simply using the maintenance plan GUI to setup transaction log backups: in SQL Server 2005, if you edit a transaction log backup task, all of your settings in that task will clear, and if it encounters a failure, the entire task fails, whereas this script skips the offending DB and moves on to the others.

While this will get around transaction log backups failing due to do databases being set to SIMPLE recovery, it doesn't help with databases that haven't had a full backup created yet. Could someone show me how to modify this script to check for the existance of a full backup, and if it doesn't exist, create a full backup, then proceed with the transaction log backup for that database?

 Thank you!


Thursday, January 09, 2014 - 10:50:56 AM - Allen Lamb Read The Tip

Do you route the maintenance plan output to the UNC path as well?

If so, can you show me how you do this?

 

thanks very much.

 

Allen

 


Thursday, January 09, 2014 - 1:00:26 PM - Greg Robidoux Read The Tip

Hi Allen, you can just change the path in the code to something like this:

\\server25\sqlbackup\

Take a look at this older tip as well: http://www.mssqltips.com/sqlservertip/1126/sql-server-backup-and-restore-to-network-drive/



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.