Determine if log backup required

Last Post 03 Mar 2013 05:31 PM by gunneyk. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Kraeg MacKenzie
New Member
New Member

--
04 Feb 2013 03:41 AM
I have backups on a FULL recovery model database running daily (full) and every 15 minutes (log). Is it possible, and if so how, to check if a log backup is required? If there has been no activity for 15 minutes, or an hour, etc, there's no need to run a log backup (is there)? I'd like to prevent a log backup being performed in that situation.
rm
New Member
New Member

--
04 Feb 2013 05:54 AM
But how do you know there's no any changes in the db in last 15 minutes?
gunneyk
New Member
New Member

--
04 Feb 2013 04:13 PM
Why do you care if you issue a log backup when there are no changes? It will only take a second and no harm is done. It's not worth the hassle of doing otherwise.
KraegM
New Member
New Member

--
03 Mar 2013 01:04 AM
I know there are no changes as the database isn't being used. When there are no changes, I'd like the backup script to detect that, and not run a log backup. For example, over the weekend it still runs a log backup every 15 minutes (because some users use the database over the weekend at times (but not every weekend))... if I have to do a restore I don't want to have to restore a bunch of log backups just-in-case; I'd rather there be no log backups when there's been no need for log backups.

However, I am working on some T-SQL that generates a restore script based on backup file contents, which would render my concern obsolete.
gunneyk
New Member
New Member

--
03 Mar 2013 05:31 PM
User interactions are not the only things that may log stuff to the tran log. There may be system level activities that get logged as well so barring using something like fn_dblog and trying to decode all that mess it isn't worth the touble. One alternative is to do periodic Differential backups on the weekends. That way if you did have to restore you simply restore the last FULL then the last DIFF and any log backups since the diff. That can save you from restoring a lot of log backups. And here is a script that may help you with automating the restores.

http://www.mssqltips.com/sqlservert...e-scripts/
You are not authorized to post a reply.

Acceptable Use Policy