Checking Backup Status on Multiple SQL Server Systems

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.

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.

-- 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

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

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.