[SQL Server] Envoyer par mail le rapport de toutes les sauvegardes SQL Server
---- =============================================
---- Author: Zouhaier KHARROUBI
---- Create date: 27 Mai 2016
---- Description: Envoyer par mail la liste des sauvegardes SQL Server
---- =============================================
CREATE PROCEDURE [dbo].[usp_BackupRapport]
@SERVEURS NVARCHAR(MAX)=NULL
--WITH EXECUTE AS OWNER
AS
SET DATEFORMAT DMY;
SET FMTONLY OFF;
DECLARE @REQUETE NVARCHAR(1000)
, @Nom_Serveur VARCHAR(150)
, @Date_Sauvegarde DATETIME
, @tableHTML NVARCHAR(MAX)
, @Separateur CHAR(1)
, @Nbre_Serveur INT
, @Num_Serveur INT
SET @Separateur =','
SET @Date_Sauvegarde = CAST(DATEADD(DAY,-1,GetDate())AS DATE)
SET NOCOUNT ON
IF EXISTS(SELECT*FROM tempdb..sysobjects WHERE id =object_id(N'[tempdb]..[#Liste_Sauvegarde]'))
BEGIN
DROP TABLE #Liste_Sauvegarde
END
IF EXISTS(SELECT*FROM tempdb..sysobjects WHERE id =object_id(N'[tempdb]..[#Liste_Serveurs]'))
BEGIN
DROP TABLE #Liste_Serveurs
END
CREATE TABLE #Liste_Sauvegarde
(
Nom_Serveur NVARCHAR(150),
Nom_Base NVARCHAR(150),
Date_Sauvegarde DATETIME
)
CREATE TABLE #Liste_Serveurs
(
Num_Serveur INT IDENTITY(1,1),
Nom_Serveur NVARCHAR(150)
)
IF @SERVEURS IS NOT NULL
BEGIN
SELECT @SERVEURS = @SERVEURS + ','
INSERT INTO #Liste_Serveurs(Nom_Serveur)
SELECT * FROM dbo.ufn_CompilerParametreChaineSSRS(@SERVEURS)
END
ELSE
BEGIN
INSERT INTO #Liste_Serveurs(Nom_Serveur)
SELECT '[' + REPLACE(REPLACE(data_source,'[',''),']','') + ']' Nom_Serveur from sys.servers
WHERE product='SQL Server'
AND data_source NOT IN('SERVEUR_TEST1','ERVEUR_TEST2','ERVEUR_TEST2')
END
SET @tableHTML = N'<H>Bonjour<BR></BR></H>'
+ N'<H>Veuillez trouver ci-dessous la liste des sauvegardes SQLServer : <BR></BR></H>'
SELECT @Nbre_Serveur = COUNT(*) FROM #Liste_Serveurs
SELECT @Num_Serveur = 1
WHILE @Num_Serveur <= @Nbre_Serveur
BEGIN
SELECT @Nom_Serveur = Nom_Serveur FROM #Liste_Serveurs WHERE Num_Serveur=@Num_Serveur
IF @Nom_Serveur <>'SERVEUR_PRINCIPAL'
BEGIN
--Utiliser OPENROWSET. Il faut activer l'option WITH EXECUTE AS OWNER
--SET @REQUETE = N'INSERT INTO #Liste_Sauvegarde SELECT ''' + @Nom_Serveur + ''' AS Nom_Serveur,'
--SET @REQUETE = @REQUETE +N'Nom_Base, Date_Sauvegarde '
--SET @REQUETE = @REQUETE +N'FROM OPENROWSET(''SQLNCLI'', ''Server='+ @Nom_Serveur +N';Trusted_Connection=yes;'','
--SET @REQUETE = @REQUETE +N'''SELECT DB.name Nom_Base, BK.type, Max(BK.backup_start_date ) Date_Sauvegarde '
--SET @REQUETE = @REQUETE +N'FROM master..sysdatabases DB '
--SET @REQUETE = @REQUETE +N'LEFT JOIN msdb..backupset BK ON DB.name=BK.database_name AND type = ''''D'''' '
--SET @REQUETE = @REQUETE +N'GROUP BY BK.database_name,BK.type'') '
--SET @REQUETE = @REQUETE + N'WHERE Nom_Base NOT IN (''tempdb'',''ReportServerTempDB'') '
--Utiliser OPENQUERY
SET @REQUETE = N'INSERT INTO #Liste_Sauvegarde SELECT ''' + @Nom_Serveur + ''' AS Nom_Serveur,'
SET @REQUETE = @REQUETE + N'Nom_Base, Date_Sauvegarde '
SET @REQUETE = @REQUETE + N'FROM OPENQUERY(' + @Nom_Serveur + N','
SET @REQUETE = @REQUETE + N'''SELECT DB.name Nom_Base, BK.type, Max(BK.backup_start_date ) Date_Sauvegarde '
SET @REQUETE = @REQUETE + N'FROM master..sysdatabases DB '
SET @REQUETE = @REQUETE + N'LEFT JOIN msdb..backupset BK ON DB.name=BK.database_name AND type = ''''D'''' '
SET @REQUETE = @REQUETE + N'GROUP BY DB.name,BK.type'') '
SET @REQUETE = @REQUETE + N'WHERE Nom_Base NOT IN (''tempdb'',''ReportServerTempDB'') '
END
ELSE
BEGIN
SET @REQUETE =N'INSERT INTO #Liste_Sauvegarde SELECT ''' + @Nom_Serveur + ''' AS Nom_Serveur,'
SET @REQUETE = @REQUETE +N'DB.name Nom_Base, Max(BK.backup_start_date) Date_Sauvegarde '
SET @REQUETE = @REQUETE +N'FROM master..sysdatabases DB '
SET @REQUETE = @REQUETE +N'LEFT JOIN msdb..backupset BK ON DB.name=BK.database_name AND type = ''''D'''' '
SET @REQUETE = @REQUETE +N'WHERE Nom_Base NOT IN (''tempdb'',''ReportServerTempDB'') '
SET @REQUETE = @REQUETE +N'GROUP BY DB.name'
END
EXEC (@REQUETE)
SET @tableHTML = @tableHTML + N'<H><b><BR><left><font color="blue" size="5">' + REPLACE(REPLACE(@Nom_Serveur,'[',''),']','') + '</left></font></BR></b></H>'
+ N'<table border="0" cellspacing="1" cellpadding="1">'
+ N'<tr><th bgcolor="#4340A4"><font color="white">N°</font></th><th bgcolor="#4340A4">
<font color="white">Base De Données</font></th><th bgcolor="#4340A4"><font color="white">Date Sauvegarde</font></th>'
SET @tableHTML = @tableHTML +
CAST(( SELECT [td/@style] =CASE WHEN CAST(ISNULL(Date_Sauvegarde,'19000101') AS DATE) < CAST(GetDate() AS DATE) THEN 'color:red; font-weight:bold' ELSE 'color:green; font-weight:bold' END,
td = ROW_NUMBER() OVER(ORDER BY T1.Nom_Serveur,Nom_Base ASC) , ' ',
[td/@style] =CASE WHEN CAST(ISNULL(Date_Sauvegarde,'19000101') AS DATE) < CAST(GetDate() AS DATE) THEN 'color:red; font-weight:bold' ELSE 'color:green; font-weight:bold' END,
td = Nom_Base, ' ',
[td/@style] = CASE WHEN CAST(ISNULL(Date_Sauvegarde,'19000101') AS DATE) < CAST(GetDate() AS DATE) THEN 'color:red; font-weight:bold' ELSE 'color:green; font-weight:bold' END,
td = Date_Sauvegarde
FROM (SELECT @Nom_Serveur Nom_Serveur) T1
LEFT JOIN #Liste_Sauvegarde T2 ON T2.Nom_Serveur=T1.NOM_SERVEUR
ORDER BY T1.Nom_Serveur,Nom_Base ASC
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) )
SET @tableHTML = @tableHTML + N'</table>'
TRUNCATE TABLE #Liste_Sauvegarde
SELECT @Num_Serveur = @Num_Serveur + 1
END
SET NOCOUNT OFF
SET @tableHTML = @tableHTML + N'<H><BR></BR></H>' +
N'<H>Merci d''analyser et résoudre les sauvegardes échouées.<BR></BR></H>' +
N'<H>Cordialement,<BR></BR></H>' +
N'<H>Service Informatique</i></H>' ;
EXEC msdb..sp_send_dbmail@profile_name='MektabaSqlServerProfilMail',@importance='High', @recipients='Cette adresse e-mail est protégée contre les robots spammeurs. Vous devez activer le JavaScript pour la visualiser.',
@subject='Backups Serveurs SQL Server', @body= @tableHTML, @body_format = 'HTML' ;