December 20, 2013 at 7:54 am
Problem - Sometimes full backups, differential or log backups fail.
The result is that the log file grows excessively.
I have a partial solution for checking all Log and Full backups at regular intervals and that is done based on recovery model.
This would send an email if the completed file does not exist.
I am looking for data in MSDB where if queried, I would be able to return whether differential backups have been set up without hard coding database or job names.
This must return whether diff backups are setup even when none have been successfully completed.
This must also not include the db if the plan used to create differential backups but currently are disabled.
I would like to run this proc on all instances without having to hard code database names or job names.
Any ideas most welcome.
Here is the partial solution
ALTER PROC CheckBackups
AS
BEGIN
DECLARE @FullBackupCompleteTime DATETIME ,
@LogBackupCompleteTime DATETIME ,
@QueryResultString VARCHAR(MAX) = ''
SELECT @FullBackupCompleteTime = ( GETDATE() - 26 / 24.0 ) ,
@LogBackupCompleteTime = ( GETDATE() - ( 60 / 60.0 / 24.0 ) )
SELECT @QueryResultString = @QueryResultString + '
No Full backup for ' + DB.NAME + ' On ' + @@SERVERNAME + ' as at '
+ CONVERT(VARCHAR(20), GETDATE())
FROM sys.databases DB
LEFT JOIN ( SELECT database_name
FROM msdb.dbo.backupset
WHERE type = 'D'
AND Backup_Finish_date > @FullBackupCompleteTime
) BackupsSets ON DB.NAME = BackupsSets.database_name
WHERE DB.state_desc = 'ONLINE'
AND db.name <> 'tempdb'
AND BackupsSets.database_name IS NULL
SELECT @QueryResultString = @QueryResultString + '
No Log backup for ' + DB.NAME + ' On ' + @@SERVERNAME + ' as at '
+ CONVERT(VARCHAR(20), GETDATE())
--DB.NAME ,
--DB.recovery_model_desc
FROM sys.databases DB
LEFT JOIN ( SELECT database_name
FROM msdb.dbo.backupset
WHERE type = 'L'
AND Backup_Finish_date > @LogBackupCompleteTime
) BackupsSets ON DB.NAME = BackupsSets.database_name
WHERE DB.state_desc = 'ONLINE'
AND db.name <> 'tempdb'
AND BackupsSets.database_name IS NULL
AND DB.recovery_model_desc = 'Full'
IF LEN(@QueryResultString) > 0
BEGIN
PRINT @QueryResultString
--EXEC msdb.dbo.sp_send_dbmail @profile_name = 'backupmonitoring',
--@recipients = 'myuser@mysite.com',
--@body = @QueryResultString,
--@subject = 'Database Backup failure list',
--@attach_query_result_as_file = 0
END
END
December 20, 2013 at 11:06 am
I put notifications on the backup jobs so when they fail I'll get notified. Why wouldn't you consider that as a solution vs. running a query to determine the state of your database backups?
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
December 20, 2013 at 11:16 am
The log only grows from failure of the log backups. It won't grow due to failed differentials or failed full backups.
Another way to audit your backups, in addition to alerts on the jobs for failures, is to check all databases for their backup age. There's even a Policy-Based Management script for that, so you don't have to write anything yourself. But if you wanted to, you could just query each database to see when they were last backed up.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 20, 2013 at 11:43 am
Kurt W. Zimmerman (12/20/2013)
I put notifications on the backup jobs so when they fail I'll get notified. Why wouldn't you consider that as a solution vs. running a query to determine the state of your database backups?Kurt
+1 This is the approach I use. it's simple and works well.
December 23, 2013 at 2:23 am
Thanks for the ideas.
We had an issue recently where a backup did not actually fail but was stuck for an extended period so the job did not complete and hence we did not get notifications.
Of course we had no idea until the activity on the DB filled up the TLog which started eating up disk from growth and we ran out of space.
So this is not a primary check, just a backup.
If you guys think that leaving the diffbackups out is fine, then I will take that advice.
December 23, 2013 at 8:03 am
Not seeing where someone said not to use differential backups or am I misunderstanding?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 23, 2013 at 2:19 pm
December 23, 2013 at 3:29 pm
MadAdmin (12/23/2013)
The log only grows from failure of the log backups. It won't grow due to failed differentials
So Diff's don't worry me too much.
A failed backup is of course top priority so I will leave that in as well as log backups.
Oh. Sorry. I'm a little slow sometimes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply