http://www.windowsitpro.com/authors/author/author/5275686/rsscomment/5275686en-USSun, 27 May 2012 06:10:52 GMTSun, 27 May 2012 06:10:52 GMThttp://www.windowsitpro.com/article/reporting2/checking-backup-status-on-multiple-sql-server-systems#commentsAnchorWed, 10 Mar 2010 15:53:45 GMT
It’s pretty bad when you post an article in SQL Server Mag and the code is bad. Great concept, but not much help when your code doesn’t work. It would be helpful if you post updated and correct code to your article.]]>TimWed, 10 Mar 2010 15:53:45 GMThttp://www.windowsitpro.com/article/reporting2/checking-backup-status-on-multiple-sql-server-systems#commentsAnchorhttp://www.windowsitpro.com/article/reporting2/checking-backup-status-on-multiple-sql-server-systems#commentsAnchorTue, 01 Dec 2009 14:17:32 GMT
I had the same problem as Marcos when trying to create usp_mon_bacukp_status_of_all_servers. Received Msg 105, Level 15, State 1, Procedure usp_mon_backup_status_of_all_servers, Line 15 Unclosed quotation mark after the character string ’ error. I tried everything I could think of. I even compared with the corrected code below, but it still didn’t work. I emailed the author just a few minutes ago. I wonder if something is getting munged for some of us when we try to copy/paste from a browser. Just a theory.]]>EdwardTue, 01 Dec 2009 14:17:32 GMThttp://www.windowsitpro.com/article/reporting2/checking-backup-status-on-multiple-sql-server-systems#commentsAnchorhttp://www.windowsitpro.com/article/reporting2/checking-backup-status-on-multiple-sql-server-systems#commentsAnchorMon, 23 Nov 2009 09:04:32 GMT
-- Replace your database name for yourdb Use Go SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO create proc usp_help_backup_status as begin declare @sql nvarchar(4000) declare @return_code int declare @last_backup_date datetime declare @server_name sysname declare servers_cursor cursor for select distinct server_name from .dbo.backup_status order by server_name open servers_cursor fetch servers_cursor into @server_name print ’ print ’---------------------------------------------------------------------------’ Print ’Databases not backed up in the last seven days’ print ’---------------------------------------------------------------------------’ print ’ while @@fetch_status = 0 begin print ’---------------------------------------------------------------------------’ print ’Server name: ’ + @server_name print ’---------------------------------------------------------------------------’ print ’ select database_name = convert(varchar, database_name), backup_finish_date = convert(varchar(30), backup_finish_date, 121), type = convert(varchar, type) from backup_status where server_name = @server_name fetch servers_cursor into @server_name end close servers_cursor deallocate servers_cursor end GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO]]>JohnMon, 23 Nov 2009 09:04:32 GMThttp://www.windowsitpro.com/article/reporting2/checking-backup-status-on-multiple-sql-server-systems#commentsAnchorhttp://www.windowsitpro.com/article/reporting2/checking-backup-status-on-multiple-sql-server-systems#commentsAnchorMon, 23 Nov 2009 09:00:34 GMT
I emailed the author and he send me updated code(see below). Works like a charm. Thanks! -John ============================================ -- Replace your database name for yourdb Use Go SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO create procedure usp_mon_backup_status_of_all_servers as begin declare @sql nvarchar(4000) declare @return_code int declare @last_backup_date datetime declare @server_name sysname declare servers_cursor cursor for select srvname from master.dbo.sysservers order by srvname delete from backup_status open servers_cursor fetch servers_cursor into @server_name while @@fetch_status = 0 begin set @sql = ’ set @sql = ’insert into backup_status SELECT server_name = ’’ + @server_name + ’’, database_name = convert(varchar, sd.name), backup_finish_date, type ’ + ’FROM [’ + @server_name + ’].master.dbo.sysdatabases sd LEFT OUTER JOIN (SELECT bs.database_name, backup_finish_date, type = case type when ’D’ then ’Database’ when ’I’ then ’Database Differential’ when ’L’ then ’Log’ when ’F’ then ’File or Filegroup’ end, backup_size FROM [’ + @server_name + ’].msdb.dbo.backupset bs, (select database_name, max_backup_finish_date = max(backup_finish_date) from [’ + @server_name + ’].msdb.dbo.backupset group by database_name) bs1 where bs.database_name = bs1.database_name and bs.backup_finish_date = bs1.max_backup_finish_date) bs3 ON bs3.database_name = sd.name where sd.name not in (’tempdb’) and (backup_finish_date < getdate() - 7 or backup_finish_date is null) ORDER BY sd.name ASC, backup_finish_date DESC’ -- print @sql exec sp_executesql @sql fetch servers_cursor into @server_name end close servers_cursor deallocate servers_cursor end GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO]]>JohnMon, 23 Nov 2009 09:00:34 GMThttp://www.windowsitpro.com/article/reporting2/checking-backup-status-on-multiple-sql-server-systems#commentsAnchorhttp://www.windowsitpro.com/article/reporting2/checking-backup-status-on-multiple-sql-server-systems#commentsAnchorWed, 18 Nov 2009 12:29:59 GMT
This is a grea idea. However, the scripts in Web Listing #1 and Web Listing #2 seem to both have errors. It isn’t very useful if the code doesn’t work.]]>JohnWed, 18 Nov 2009 12:29:59 GMThttp://www.windowsitpro.com/article/reporting2/checking-backup-status-on-multiple-sql-server-systems#commentsAnchorhttp://www.windowsitpro.com/article/reporting2/checking-backup-status-on-multiple-sql-server-systems#commentsAnchorTue, 20 Oct 2009 14:13:58 GMT
I think that there is a typo for the usp_mon_backup_status_of_all_servers. When I tried to create it I get the following error: Msg 105, Level 15, State 1, Procedure usp_mon_backup_status_of_all_servers, Line 46 Unclosed quotation mark after the character string ’ Also, when I tried to create a Master SQL server to monitor a test server I get the following error: =================================== MSX enlist failed for JobServer ’TESTSQL1’. (Microsoft.SqlServer.Smo) ------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+((Katmai_PCU_Main).090329-101 5+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=MSX+enlist +JobServer&LinkId=20476 ------------------------------ Program Location: at Microsoft.SqlServer.Management.Smo.Agent.JobServer.MsxEnlist(String masterServer, String location) at Microsoft.SqlServer.Management.SqlManagerUI.EnlistTsxActions.DoAction(ProgressItemCollection actions, Int32 index) at Microsoft.SqlServer.Management.SqlStudio.Controls.ProgressItemCollection.DoWorkOnThread() =================================== The web link for help had the typical Microsoft message ’We’re sorry’ but I did find some information that might be related to this problem. http://msdn.microsoft.com/en-us/library/ms365379.aspx I am not sure if I should be making any changes to the MsxEncryptChannelOptions registry entry. It wouldn’t be a wizard if you had to mess around with the regisry. Please let me know if I am doing something wrong, I would like to implement this for my SQL enviorment. Thank You]]>RayTue, 20 Oct 2009 14:13:58 GMThttp://www.windowsitpro.com/article/reporting2/checking-backup-status-on-multiple-sql-server-systems#commentsAnchorhttp://www.windowsitpro.com/article/reporting2/checking-backup-status-on-multiple-sql-server-systems#commentsAnchorFri, 16 Oct 2009 09:00:15 GMT
Nice to have, I just wonder if it will works fine for thousands of servers. Take Care!]]>MarcosFri, 16 Oct 2009 09:00:15 GMThttp://www.windowsitpro.com/article/reporting2/checking-backup-status-on-multiple-sql-server-systems#commentsAnchorhttp://www.windowsitpro.com/article/reporting2/checking-backup-status-on-multiple-sql-server-systems#commentsAnchorThu, 08 Oct 2009 13:01:47 GMT
excellent]]>DianeThu, 08 Oct 2009 13:01:47 GMThttp://www.windowsitpro.com/article/reporting2/checking-backup-status-on-multiple-sql-server-systems#commentsAnchor