Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have the following set up as a sp and it runs daily. It gives me a report for each SQL cluster in my environment but what I'd like to do is configure it to run across all clusters and return 1 report with the data from all of the clusters. For example I've got 6 clusters throughout my org and I get 6 reports daily on back up aging. I'd like to be able to get 1 report with the info from each. The following is my sp.

USE [IT_MAINTENANCE]
GO
/****** Object:  StoredProcedure [dbo].[BackupAgingReport]    Script Date: 12/20/2013 10:22:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--exec dbo.BackupAgingReport @emailTo = 'email address'

-- =============================================
-- Author:      IT
-- Create date: 2013-12-18
-- Description: Backup Aging Report,  Send e-mail in HTML Format
--
-- Version History
--   2013-12-18: added sys.databases.status_desc = 'ONLINE' filter
-- =============================================
ALTER PROCEDURE [dbo].[BackupAgingReport]
    @emailTo varchar(250) = 'email address',
    @ageFull int = 168, -- 1Week
    @ageDiff int = 24,  -- 24 Hours
    @ageLog  int = 60   -- 30 Minutes
AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @tableHTML  NVARCHAR(MAX) ;

    SET @tableHTML =
    N'<H1>Full Backup Aging Report</H1>' +
    N'<i>Threshold: ' + Convert(varchar(10),@ageFull) + ' Hours</i>' +
    N'<table border="1">' +
    N'<tr><th>Server</th><th>Database</th><th>Last Backup</th><th>Backup Age Hours</th></tr>' +
    IsNull(CAST ( ( SELECT 
                td = CONVERT(CHAR(100), SERVERPROPERTY('Servername')) , '', 
                td = msdb.dbo.backupset.database_name,  '', 
                td = convert(varchar(10),MAX(msdb.dbo.backupset.backup_finish_date),120) ,  '', 
                td = DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE())  
                FROM    msdb.dbo.backupset inner join sys.databases on msdb.dbo.backupset.database_name = sys.databases.name 
                WHERE     msdb.dbo.backupset.type = 'D' and
                    sys.databases.state_desc = 'ONLINE'
                GROUP BY msdb.dbo.backupset.database_name 
                HAVING      (MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(hh, - @ageFull, GETDATE()))  
                ORDER BY  msdb.dbo.backupset.database_name
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ), '') +
    N'</table>' +

    N'<H1>Missing Full Backup Report</H1>' +
    N'<table border="1">' +
    N'<tr><th>Server</th><th>Database</th><th>Last Backup</th><th>Backup Age Hours</th></tr>' +
    IsNull(CAST ( (  
                SELECT      
                   td = CONVERT(CHAR(100), SERVERPROPERTY('Servername')),   '', 
                   td = master.dbo.sysdatabases.NAME,   '', 
                   td = 'Never',   '', 
                   td = 9999   
                FROM 
                   master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset 
                       ON master.dbo.sysdatabases.name  = msdb.dbo.backupset.database_name 
                WHERE msdb.dbo.backupset.database_name IS NULL AND master.dbo.sysdatabases.name <> 'tempdb' 
                ORDER BY  msdb.dbo.backupset.database_name
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ),'') +              
    N'</table>' +

    N'<H1>Diff Backup Aging Report</H1>' +
    N'<i>Threshold: ' + Convert(varchar(10),@ageDiff) + ' Hours</i>' +
    N'<table border="1">' +
    N'<tr><th>Server</th><th>Database</th><th>Last Backup</th><th>Backup Age Hours</th></tr>' +
    isnull(CAST ( ( SELECT 
                td = CONVERT(CHAR(100), SERVERPROPERTY('Servername')) , '', 
                td = msdb.dbo.backupset.database_name,  '', 
                td = convert(varchar(10),MAX(msdb.dbo.backupset.backup_finish_date),120) ,  '', 
                td = DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE())  
                FROM    msdb.dbo.backupset inner join sys.databases on msdb.dbo.backupset.database_name = sys.databases.name 
                WHERE     msdb.dbo.backupset.type = 'I'  and
                    sys.databases.state_desc = 'ONLINE'
                GROUP BY msdb.dbo.backupset.database_name 
                HAVING      (MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(hh, - @ageDiff, GETDATE()))  
                ORDER BY  msdb.dbo.backupset.database_name
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ), '') +
    N'</table>' +

    N'<H1>Log Backup Aging Report</H1>' +
    N'<i>Threshold: ' + Convert(varchar(10),@ageLog) + ' Minutes</i>' +
    N'<table border="1">' +
    N'<tr><th>Server</th><th>Database</th><th>Last Backup</th><th>Backup Age Minutes</th></tr>' +
    isnull(CAST ( ( SELECT 
                td = CONVERT(CHAR(100), SERVERPROPERTY('Servername')) , '', 
                td = msdb.dbo.backupset.database_name,  '', 
                td = convert(varchar(16),MAX(msdb.dbo.backupset.backup_finish_date),120) ,  '', 
                td = DATEDIFF(mi, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE())  
                FROM    msdb.dbo.backupset inner join sys.databases on msdb.dbo.backupset.database_name = sys.databases.name 
                WHERE     msdb.dbo.backupset.type = 'L'  and
                    sys.databases.state_desc = 'ONLINE'
                GROUP BY msdb.dbo.backupset.database_name 
                HAVING      (MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(mi, - @ageLog, GETDATE()))  
                ORDER BY  msdb.dbo.backupset.database_name
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ), '') +
    N'</table>' 

    --print @tableHTML
DECLARE @eSubject varchar(250)
SET @eSubject = 'Backup Aging Report (' + Convert(varchar(250),SERVERPROPERTY('Servername')) + ')'    
EXEC msdb.dbo.sp_send_dbmail @recipients=@emailTo,
    @subject = @eSubject,
    @body = @tableHTML,
    @body_format = 'HTML' ;

END

*EDIT**

Now that I have my tbl_FullBackupAging set up and I'm using a SP how do I insert into my tbl if it's coming from a different SQL Server?

Here's basically what my SP looks like.

Declare @ageFull int = 168 --1Week
INSERT INTO [SQLSERVER].[IT_MAINTENANCE].[dbo].[tbl_FullBackupAging]
SELECT 
                CONVERT(CHAR(100), SERVERPROPERTY('Servername'))  As Server, 
                msdb.dbo.backupset.type AS 'Type',
                msdb.dbo.backupset.database_name AS 'Database', 
                convert(varchar(10),MAX(msdb.dbo.backupset.backup_finish_date),120) AS 'Last Backup', 
                DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE()) AS 'Backup Age Hours' 
                FROM msdb.dbo.backupset inner join sys.databases on msdb.dbo.backupset.database_name = sys.databases.name 
                WHERE msdb.dbo.backupset.type = 'D' and
                    sys.databases.state_desc = 'ONLINE'
                GROUP BY msdb.dbo.backupset.database_name, msdb.dbo.backupset.type 
                HAVING (MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(hh, - @ageFull, GETDATE()))  
                ORDER BY msdb.dbo.backupset.database_name

INSERT INTO [SQLSERVER].[IT_MAINTENANCE].[dbo].[tbl_FullBackupAging]
SELECT      
                CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS 'Server', 
                'NEVER' AS 'Type',
                master.dbo.sysdatabases.NAME AS 'Database', 
                'Never' AS 'Last Backup', 
                9999 AS 'Backup Age Hours'  
                FROM 
                   master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset 
                       ON master.dbo.sysdatabases.name  = msdb.dbo.backupset.database_name 
                WHERE msdb.dbo.backupset.database_name IS NULL AND master.dbo.sysdatabases.name <> 'tempdb' 
                ORDER BY  msdb.dbo.backupset.database_name

DECLARE @ageDiff int = 24 --24Hours 
INSERT INTO [SQLSERVER].[IT_MAINTENANCE].[dbo].[tbl_FullBackupAging]            
SELECT 
                CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS 'Server', 
                msdb.dbo.backupset.type AS 'Type',
                msdb.dbo.backupset.database_name AS 'Database', 
                convert(varchar(10),MAX(msdb.dbo.backupset.backup_finish_date),120) AS 'Last Backup', 
                DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE()) AS 'Backup Age Hours' 
                FROM msdb.dbo.backupset inner join sys.databases on msdb.dbo.backupset.database_name = sys.databases.name 
                WHERE msdb.dbo.backupset.type = 'I'  and
                    sys.databases.state_desc = 'ONLINE'  and NOT(msdb.dbo.backupset.database_name) IN ('model', 'msdb')
                GROUP BY msdb.dbo.backupset.database_name, msdb.dbo.backupset.type
                HAVING (MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(hh, - @ageDiff, GETDATE()))  
                ORDER BY  msdb.dbo.backupset.database_name    


DECLARE @ageLog int = 60 --60Minutes
INSERT INTO [SQLServer].[IT_MAINTENANCE].[dbo].[tbl_FullBackupAging]    
SELECT 
                CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS 'Server', 
                msdb.dbo.backupset.type AS 'Type',
                msdb.dbo.backupset.database_name AS 'Database', 
                convert(varchar(16),MAX(msdb.dbo.backupset.backup_finish_date),120) AS 'Last Backup', 
                DATEDIFF(mi, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE()) AS 'Backup Age Hours' 
                FROM msdb.dbo.backupset inner join sys.databases on msdb.dbo.backupset.database_name = sys.databases.name 
                WHERE msdb.dbo.backupset.type = 'L'  and
                    sys.databases.state_desc = 'ONLINE'  and NOT(msdb.dbo.backupset.database_name) IN ('model', 'msdb')
                GROUP BY msdb.dbo.backupset.database_name, msdb.dbo.backupset.type 
                HAVING (MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(mi, - @ageLog, GETDATE()))  
                ORDER BY  msdb.dbo.backupset.database_name
share|improve this question

1 Answer 1

up vote 1 down vote accepted

what you can do is

On all the clusters create a table with same schema as your output. e.g. tbl_BackupAgingReport

Server | Database | Last Backup | Backup Age Hours

Then insert into that table by executing the SP.

-- truncate table before the next run and then insert
insert into tbl_BackupAgingReport
exec [dbo].[BackupAgingReport]

Now you can insert that data into a central server where you can insert using opendatasource

--Insert back into reporting server and then you can use all the fancy HTML tags to do reporting.

    declare @query nvarchar(max)
    select @query = 'insert opendatasource (''' + 'SQLOLEDB''' + ',''Data Source=ip,port; User ID=sa;Password=strong_password'''+ ').IT_MAINTENANCE.dbo.BackupAgingReport_Final(Server, Database, Last Backup,Backup Age Hours)'+char(10)+
    'select * from tbl_BackupAgingReport'
 print @query
 --exec (@query)

Alternatively, use powershell.

share|improve this answer
    
That seems a bit intense. maybe I'm a little too new to this. is there a way that I can just alter the script to run against my 6 different servers to return 1 report? –  BSanders Dec 23 '13 at 20:05
    
@BSanders thats the way that I can think of using T-SQL. You can use Powershell if you want. You just need a way to dump all the results into a central database table, and then you can generate reports out of that. –  Kin Dec 23 '13 at 20:29
    
Thank you Kin! I set up the tbl and it works perfectly except for if it's coming from a different SQL Server. Can you help me to better understand how I'd work that? –  BSanders Dec 24 '13 at 19:23
    
On the other servers, you have to put in the opendatasource pointing to the central server where you will be running the report from. Also, the ip,port should be the central server ip,port. That way the SQL gets executed on the source servers, but the results get dumped into the central server - the one you will be generating reports from. –  Kin Dec 24 '13 at 21:58
    
I think I have this all set up. I just have to get a SQL user set up for the opendatasource string. –  BSanders Dec 27 '13 at 15:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.