[SQL Server] Afficher l'historique des sauvegardes de tous les serveurs SQL Server liés
/*
Auteur : KHARROUBI Zouhaier
Date Création : 01/06/2009
Objectif : afficher le rapport des sauvegardes des serveurs SQLServer
--Important : le contenu du parametre @SERVEURS doit etre comme le suivant : 'SQLSERVER_1,SQLSERVER_2,SQLSERVER_3,SQLSERVER_4,'
*/
CREATE PROCEDURE [dbo].[usp_BackupRapport]
@SERVEURS NVARCHAR(100)=NULL
WITHEXECUTEASOWNER
AS
SETDATEFORMAT DMY
DECLARE @LISTE_SERVEUR VARCHAR(MAX)
DECLARE @REQUETE NVARCHAR(1000)
DECLARE @NOM_SRV_LIEN VARCHAR(35)
DECLARE @NOM_SERVEUR VARCHAR(35)
DECLARE @SEPARATEUR CHAR(1)
DECLARE @POSITION INT
DECLARE @DEBUT INT
DECLARE @TAILLE INT
DECLARE @BACKUP_START_DATE DATETIME
SET @SEPARATEUR =','
SET @BACKUP_START_DATE =CAST(FLOOR(CAST(DATEADD(DAY,-1,GetDate())ASFLOAT))ASDATETIME)
SETNOCOUNTON
IFEXISTS(SELECT*FROM tempdb..sysobjectsWHERE id =object_id(N'[tempdb]..[#BACKUP_DERNIER]'))
DROPTABLE #BACKUP_DERNIER
CREATETABLE #BACKUP_DERNIER
(
SERVER_NAME NVARCHAR(35),
DBASE_NAME NVARCHAR(35),
BACKUP_START_DATE DATETIME
)
IF @SERVEURS ISNULLOR @SERVEURS='ALL'
BEGIN
SET @LISTE_SERVEUR ='SQLSERVER_1'+ @SEPARATEUR +'SQLSERVER_2'+ @SEPARATEUR +'SQLSERVER_3'+ @SEPARATEUR
END
ELSE
SET @LISTE_SERVEUR=@SERVEURS + @SEPARATEUR
SET @POSITION =CHARINDEX(@SEPARATEUR, @LISTE_SERVEUR)
SET @DEBUT = 1
WHILE @POSITION <=LEN(@LISTE_SERVEUR)AND @POSITION>0
BEGIN
SET @TAILLE=@POSITION-@DEBUT
SET @NOM_SRV_LIEN =SubString(@LISTE_SERVEUR, @DEBUT, @TAILLE )
SET @DEBUT = @POSITION + 1
SET @POSITION =CHARINDEX(@SEPARATEUR, @LISTE_SERVEUR,@POSITION+1)
SELECT @NOM_SERVEUR = dbo.ServerName(@NOM_SRV_LIEN)
IF @NOM_SERVEUR <>'SQLSERVER_1'
BEGIN
SET @REQUETE = N'INSERT INTO #BACKUP_DERNIER SELECT ''' + @NOM_SERVEUR + ''' AS ServerName,'
SET @REQUETE = @REQUETE +N'database_name, backup_start_date '
SET @REQUETE = @REQUETE +N'FROM OPENROWSET(''SQLNCLI'', ''Server='+ @NOM_SRV_LIEN +N';Trusted_Connection=yes;'','
SET @REQUETE = @REQUETE +N'''SELECT BK.database_name, BK.type, Max(BK.backup_start_date) backup_start_date '
SET @REQUETE = @REQUETE +N'FROM msdb..backupset BK '
SET @REQUETE = @REQUETE +N'JOIN master..sysdatabases DB ON DB.name=BK.database_name '
SET @REQUETE = @REQUETE +N'WHERE type = ''''D'''''
SET @REQUETE = @REQUETE +N'GROUP BY BK.database_name,BK.type'')'
--SET @REQUETE = N'INSERT INTO #BACKUP_DERNIER SELECT ''' + @NOM_SERVEUR + ''' AS ServerName,'
--SET @REQUETE = @REQUETE + N'database_name, backup_start_date '
--SET @REQUETE = @REQUETE + N'FROM OPENQUERY(' + @NOM_SRV_LIEN + N','
--SET @REQUETE = @REQUETE + N'''SELECT BK.database_name, BK.type, Max(BK.backup_start_date) backup_start_date '
--SET @REQUETE = @REQUETE + N'FROM msdb..backupset BK '
--SET @REQUETE = @REQUETE + N'JOIN master..sysdatabases DB ON DB.name=BK.database_name '
--SET @REQUETE = @REQUETE + N'GROUP BY BK.database_name,BK.type''' + ') '
--SET @REQUETE = @REQUETE + N'WHERE type = ''' + 'D' + ''''
END
Else
BEGIN
SET @REQUETE =N'INSERT INTO #BACKUP_DERNIER SELECT '''+'SQLSERVER_1'+''' AS ServerName,'
SET @REQUETE = @REQUETE +N'BK.database_name, Max(BK.backup_start_date) backup_start_date '
SET @REQUETE = @REQUETE +N'FROM msdb..backupset BK '
SET @REQUETE = @REQUETE +N'JOIN master..sysdatabases DB ON DB.name=BK.database_name '
SET @REQUETE = @REQUETE +N'WHERE BK.type = '''+'D '+''''
SET @REQUETE = @REQUETE +N'GROUP BY BK.database_name'
END
--PRINT (@REQUETE)
EXEC (@REQUETE)
END
SETNOCOUNTOFF
SELECT*FROM #BACKUP_DERNIER
--WHERE backup_start_date >= @BACKUP_START_DATE
ORDERBY SERVER_NAME,DBASE_NAME ASC
DROP TABLE #BACKUP_DERNIER