InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 07:45:29 GMT20http://www.sqlservercentral.com/Forums/Topic1392673-392-1.aspxOut of curiosity can you post which solution you used once you get one in place?Wed, 05 Dec 2012 08:08:52 GMTCapnHectorhttp://www.sqlservercentral.com/Forums/Topic1392673-392-1.aspxThank you everyone for your fast response and code posting, now with a quit afternoon(not likely). I can get this loaded throughout my universe.Wed, 05 Dec 2012 06:50:11 GMTSirFalcon60http://www.sqlservercentral.com/Forums/Topic1392673-392-1.aspxHave a look at Policy Based Management.I set my policies up as "On Demand", and schedule the evaluations from a Central Management Server. This allows me to check my 2005 servers as well.But as you say all your servers are 2008+, you could create them with "On Schedule" evaluation modes so that each instance checks itself regularly. A failure results in an error in the log, which can be alerted on using a SQL Agent Alert.[url=http://msdn.microsoft.com/en-us/library/bb510667(v=sql.105).aspx]http://msdn.microsoft.com/en-us/library/bb510667(v=sql.105).aspx[/url]Tue, 04 Dec 2012 17:12:59 GMTRichard Fryarhttp://www.sqlservercentral.com/Forums/Topic1392673-392-1.aspxwe actually schedule our check via SCOM, so if you have an enterprise monitoring tool like SCOM you would hopefully be able to plug the script into it.Tue, 04 Dec 2012 15:20:58 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic1392673-392-1.aspxto expand on what george posted you can create a procedure and schedule an agent job to run it. the procedure would also email the results (if any) using sp_send_dbmail [url]http://msdn.microsoft.com/en-us/library/ms190307.aspx[/url]im currently working on some example code and will post when im doneEDIT:Here is a rough sample and will probably need to be tweaked to make it work specifically as you want so it will email you through the dbmail system if there is an error. you would just set up an agent job to run the SP once a day[code="sql"]CREATE PROCEDURE usp_NoTlogBackup (@Length INT)ASDECLARE @BadDatabases VARCHAR(8000)DECLARE @Body VARCHAR(8000)SELECT @BadDatabases = STUFF((select ', ' + cast(a.database_name as varchar(100)) from ( select [name] as database_name from master.dbo.sysdatabases where databasepropertyex([name], 'recovery') in ('FULL','BULK_LOGGED') and databasepropertyex([name], 'isinstandby') = 0 and databasepropertyex([name], 'status') = 'online' and databasepropertyex([name], 'updateability') = 'read_write' and [name] not in ('model','tempdb') ) a left join msdb.dbo.backupset b on a.database_name = b.database_name and b.type='L' and datediff(hour,b.backup_finish_date,getdate()) < @Length where b.database_name is nullFOR XML PATH ('')),1,2,'')IF (@BadDatabases IS NOT NULL)BEGINSET @Body = 'On ' + @@SERVERNAME + ' The following Databases do not have Tlog backups: ' + @BadDatabasesEXEC msdb.dbo.sp_send_dbmail @profile_name = 'profile_name', @recipients = 'your@email.com', @from_address = 'ServerName@from.com', @body = @BodyEND[/code]Tue, 04 Dec 2012 15:03:45 GMTCapnHectorhttp://www.sqlservercentral.com/Forums/Topic1392673-392-1.aspx[code="sql"]-- GET DATABASES WITHOUT FULL OR TLOG BACKUPS-- dbs with no full backups in last week (select cast(a.[name] as varchar(100)) as db_nm into #no_full_backup from master.dbo.sysdatabases a left join msdb.dbo.backupset b on a.[name] = b.database_name and datediff(hour,b.backup_finish_date,getdate())<168 -- 1 week and b.type='D' where a.[name] != 'tempdb' and databasepropertyex(a.[name], 'isinstandby') = 0 and databasepropertyex(a.[name], 'status') = 'online' and databasepropertyex(a.[name], 'updateability') = 'read_write' and b.database_name is null )-- dbs with no transaction log backup for a day (select cast(a.database_name as varchar(100)) as db_nm into #no_tlog_backup from ( select [name] as database_name from master.dbo.sysdatabases where databasepropertyex([name], 'recovery') in ('FULL','BULK_LOGGED') and databasepropertyex([name], 'isinstandby') = 0 and databasepropertyex([name], 'status') = 'online' and databasepropertyex([name], 'updateability') = 'read_write' and [name] not in ('model','tempdb') ) a left join msdb.dbo.backupset b on a.database_name = b.database_name and b.type='L' and datediff(hour,b.backup_finish_date,getdate())<24 where b.database_name is null)--select * from #no_full_backup--select * from #no_tlog_backupif (select count(*) from #no_full_backup) > 0 raiserror ('databases with no full backups in last 7 days exist',16,1) with logif (select count(*) from #no_tlog_backup) > 0 raiserror ('databases with no transaction backups in last 1 days exist',16,1) with logdrop table #no_full_backupdrop table #no_tlog_backup[/code]This one is actually SQL2000 compatible but works on other versions. You would be well to get the database status values from table master.sys.databasesTue, 04 Dec 2012 14:53:19 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic1392673-392-1.aspxDear Forum, I'm sure someone has completed this type of request, but here is the back ground. I have a thrid party tool to complete all forms of backups on SQL databases. I have recently found that from time to time this 3rd party backup tool will drop a backup schdule. For example I have a instance where Transaction Log backups have not occured in months. So now I must Mirco manage the backups across some 600 instances. My questions does any have the code or an example on how send or create an alert if a database has not had a log backup with in the last 24 hours?All Enviroments are either 2K8 or 2K8R2.Any help or direction would be gladly execpted.Fishing for help..:hehe:Tue, 04 Dec 2012 13:09:18 GMTSirFalcon60