[SQL Server] Afficher l'historique des sauvegardes de tous les serveurs SQL Server liés Méthode 2
-- =============================================
-- Author: Zouhaier KHARROUBI
-- Create date: 16/02/2019
-- Description: Afficher l'historique des sauvegardes de tous les serveurs SQL Server liés Méthode 2
-- =============================================
--Add the parameters for the stored procedure here
DECLARE @Date_BackupASDATE=NULL
,@backup_status ASVARCHAR(3)=NULL-- ALL , OK, KO
,@Backup_type ASCHAR(1)=NULL-- D , L , I
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SETNOCOUNTON;
-- Insert statements for procedure here
IFEXISTS(SELECT*FROM tempdb..sysobjectsWHERE id =object_id(N'[tempdb]..[#DB_EXCLURE]'))
BEGIN
DROPTABLE #DB_EXCLURE
END
IFEXISTS(SELECT*FROM tempdb..sysobjectsWHERE id =object_id(N'[tempdb]..[#Serveurs_LiesTemp]'))
BEGIN
DROPTABLE #Serveurs_LiesTemp
END
IFEXISTS(SELECT*FROM tempdb..sysobjectsWHERE id =object_id(N'[tempdb]..[#Serveurs_Lies]'))
BEGIN
DROPTABLE #Serveurs_Lies
END
IFEXISTS(SELECT*FROM tempdb..sysobjectsWHERE id =object_id(N'[tempdb]..[#Resultat]'))
BEGIN
DROPTABLE #Resultat
END
IFEXISTS(SELECT*FROM tempdb..sysobjectsWHERE id =object_id(N'[tempdb]..[#Objets]'))
BEGIN
DROPTABLE #Objets
END
CREATETABLE #DB_EXCLURE
(
Nom_Base VARCHAR(150)NULL
)
CREATENONCLUSTEREDINDEX [IDX_#DB_EXCLURE_Nom_Base] ON #DB_EXCLURE
(
Nom_Base ASC
)
CREATETABLE #Serveurs_LiesTemp
(
SRV_NAME VARCHAR(150),
SRV_PROVIDERNAME VARCHAR(150),
SRV_PRODUCT VARCHAR(150),
SRV_DATASOURCE VARCHAR(150),
SRV_PROVIDERSTRING VARCHAR(150),
SRV_LOCATION VARCHAR(150),
SRV_CAT VARCHAR(150)
)
CREATETABLE #Serveurs_Lies
(
Num_Lig INTIDENTITY (1,1),
SRV_NAME VARCHAR(150),
SRV_DATASOURCE VARCHAR(150),
ServerName VARCHAR(150)NULL
)
CREATENONCLUSTEREDINDEX [IDX_#Serveurs_Lies_ServerName] ON #Serveurs_Lies
(
ServerName ASC
)
CREATETABLE #Resultat
(
Num_Lig INTIDENTITY (1,1),
Serveur_Lie VARCHAR(150) NULL,
RequeteBck NVARCHAR(MAX)NULL,
Environnement VARCHAR(100)NULL,
server_name VARCHAR(150) NULL,
machine_name VARCHAR(150)NULL,
[database_name] VARCHAR(150) NULL,
backup_job_enabled TINYINT NULL,
backup_status VARCHAR(2) NULL,
[type] CHAR(1) NULL,
backup_start_date DATETIME NULL,
backup_finish_date DATETIME NULL,
physical_device_nameVARCHAR(MAX)NULL,
backup_path VARCHAR(MAX) NULL,
CommandBackup VARCHAR(MAX) NULL,
LocalReplicaRole TINYINT NULL,
backup_genere BIT NULL
)
CREATENONCLUSTEREDINDEX [IDX_#Resultat_LocalReplicaRole] ON #Resultat
(
LocalReplicaRole ASC
)
CREATENONCLUSTEREDINDEX [IDX_#Resultat_Environnement_server_name_database_name] ON #Resultat
(
Environnement ASC,
server_name ASC,
[database_name] ASC
)
CREATENONCLUSTEREDINDEX [IDX_#Resultat_backup_status] ON #Resultat
(
backup_status ASC
)
CREATETABLE #Objets
(
Nom_Objet VARCHAR(150)NULL
)
CREATENONCLUSTEREDINDEX [IDX_#Objets_Nom_Objet] ON #Objets
(
Nom_Objet ASC
)
DECLARE @Backup_Param ASVARCHAR(35)
,@Backup_Abrg ASVARCHAR(5)
,@Date_Backup_Is_Null ASBIT= 0
,@Num_Lig ASINT
,@Nbre_Lig ASINT
,@Serveur_Lie ASVARCHAR(150)
,@CommandBackup ASVARCHAR(300)
,@Requete ASNVARCHAR(MAX)
,@LocalHot ASVARCHAR(50)
,@ServerName ASVARCHAR(150)
SELECT @LocalHot =QUOTENAME(@@SERVERNAME)+'.'
IF @Date_Backup ISNULL
BEGIN
SELECT @Date_Backup =GETDATE()
,@Date_Backup_Is_Null = 1
END
IF @backup_status ISNULL
BEGIN
SELECT @backup_status ='KO'
END
IFDATEPART(WEEKDAY, @Date_Backup )=2 --Le lundi vérifier les backup FULL qui sont lancés tous les Vendredi à 20:05
BEGIN
SELECT @Date_Backup =CASE @Date_Backup_Is_Null WHEN 1 THENCAST(DATEADD(DAY,-3,@Date_Backup)ASDATE)ELSE @Date_Backup END
,@Backup_type=ISNULL(@Backup_type,'D')
END
ELSE--Les autres jours vérifier les backup DIFF qui sont lancés tous les soir à 20:00
BEGIN
SELECT @Date_Backup =CASE @Date_Backup_Is_Null WHEN 1 THENCAST(DATEADD(DAY,-1,@Date_Backup)ASDATE)ELSE @Date_Backup END
,@Backup_type=ISNULL(@Backup_type,'I')
END
SELECT @Backup_Param =CASE @Backup_type WHEN'D'THEN 'BACKUP_FULL'
WHEN'I'THEN 'BACKUP_DIFF'
ELSE 'BACKUP_LOG'
END
SELECT @Backup_Abrg =CASE @Backup_type WHEN'D'THEN 'FULL'
WHEN'I'THEN'DIFF'
ELSE 'LOG'
END
IF DATEPART(WEEKDAY, @Date_Backup )=6 --La vérification correspond aux backups du Vendredi
BEGIN
INSERTINTO #DB_EXCLURE(Nom_Base)VALUES ('tempdb')
END
ELSE
BEGIN
INSERTINTO #DB_EXCLURE(Nom_Base)VALUES ('tempdb'),('master')
END
INSERTINTO #Serveurs_LiesTemp
EXECsp_linkedservers
INSERTINTO #Serveurs_Lies(SRV_NAME,SRV_DATASOURCE)
SELECTDISTINCT SRV_NAME,SRV_DATASOURCE FROM #Serveurs_LiesTemp WHERE SRV_NAME NOTIN('Serveur_1','Serveur_2')
--AND SRV_NAME='Serveur_3'
SELECT @Num_Lig=1,@Nbre_Lig=COUNT(Num_Lig)FROM #Serveurs_Lies
WHILE @Num_Lig <= @Nbre_Lig
BEGIN
BEGINTRY
SELECT @Serveur_Lie =SRV_DATASOURCE FROM #Serveurs_Lies WHERE Num_Lig=@Num_Lig
SELECT @Requete =N'SELECT @ServerName_Out=ServerName FROM OPENQUERY('+QUOTENAME(@Serveur_Lie)+N',''SELECT @@SERVERNAME AS ServerName'')'
EXECUTEsp_executesql@Requete,N'@ServerName_Out VARCHAR(150) OUTPUT',@ServerName_Out=@ServerName OUTPUT
IFNOTEXISTS(SELECT ServerName FROM #Serveurs_Lies WHEREISNULL(ServerName,'')=@ServerName)
BEGIN
UPDATE #Serveurs_Lies
SET
ServerName=@ServerName
WHERE Num_Lig=@Num_Lig
TRUNCATETABLE #Objets
SELECT @Requete =N'SELECT TOP 1 [name] AS Nom_Objet FROM '+QUOTENAME(@Serveur_Lie)+N'.[master].[sys].[all_objects] WHERE [name] LIKE ''availability%'''
INSERTINTO #Objets(Nom_Objet)
EXECUTEsp_executesql@Requete
SELECT @Requete =CASEWHEN @LocalHot<>@Serveur_Lie THENN'SELECT * FROM OPENQUERY('+QUOTENAME(@Serveur_Lie)+','''ELSE''END
SELECT @Requete = @Requete +N'SELECT DISTINCT '''''+ @Serveur_Lie +''''' AS Serveur_Lie,ISNULL(T2.server_name ,T6.SERVERNAME) AS server_name'+' ,ISNULL(T2.machine_name,T6.SERVERNAME) AS machine_name,T1.[name] AS [database_name],T8.backup_job_enabled,backup_status = CASE WHEN T3.physical_device_name IS NULL THEN ''''KO'''' ELSE ''''OK'''' END,ISNULL(T2.[type],'''''+ @Backup_type +N''''') AS Backup_Type'
+N',T2.backup_start_date,T2.backup_finish_date,T3.physical_device_name,'+'T7.backup_path + T1.[name] + ''''\'''' + '''''+ @Backup_Abrg +'\'''' AS backup_path,T4.CommandBackup,T5.LocalReplicaRole,backup_genere = CASE WHEN T3.physical_device_name LIKE ''''\\%.bak'''' OR T3.physical_device_name LIKE ''''\\%.sqb'''' THEN 1 ELSE 0 END
FROM master.dbo.sysdatabases T1'
+N' LEFT JOIN [msdb].[dbo].[backupset] T2 ON T2.[database_name]=T1.[name] AND CONVERT(VARCHAR(10),T2.backup_start_date,112)>='''''+CONVERT(VARCHAR(10), @Date_Backup,112)+''''' AND T2.[type]='''''+ @Backup_type +''''''
+N' LEFT JOIN [msdb].[dbo].[backupmediafamily] T3 ON T3.media_set_id=T2.media_set_id'
+N' CROSS APPLY ('
+N'SELECT TOP 1 Command AS CommandBackup
FROM [msdb].[dbo].[sysjobsteps]
WHERE command LIKE ''''%COMP_LOG_BACKUP%'''' OR command LIKE ''''%COMP_DIFF_BACKUP%''''
OR command LIKE ''''%COMP_FULL_BACKUP%'''''
+N' ) T4'
IFEXISTS(SELECT Nom_Objet FROM #Objets)
BEGIN
SELECT @Requete = @Requete +N' LEFT JOIN (SELECT S1.name AS [AvailabilityGroupName],
ISNULL(S2.primary_replica, '''''''') AS [PrimaryReplicaServerName],
ISNULL(S4.role, 3) AS [LocalReplicaRole], S5.database_name AS [DatabaseName]
,S4.role_desc,S4.operational_state,S4.operational_state_desc
FROM [master].[sys].[availability_groups] AS S1
LEFT OUTER JOIN [master].[sys].[dm_hadr_availability_group_states] as S2 ON S1.group_id = S2.group_id
INNER JOIN [master].[sys].[availability_replicas] AS S3 ON S1.group_id = S3.group_id
INNER JOIN [master].[sys].[dm_hadr_availability_replica_states] AS S4 ON S3.replica_id = S4.replica_id AND S4.is_local = 1
INNER JOIN [master].[sys].[dm_hadr_database_replica_cluster_states] AS S5 ON S4.replica_id = S5.replica_id
LEFT OUTER JOIN [master].[sys].[dm_hadr_database_replica_states] AS S6 ON S5.replica_id = S6.replica_id AND S5.group_database_id = S6.group_database_id
) T5 ON T5.[DatabaseName]=T1.[name] COLLATE French_CI_AS'
END
ELSE
BEGIN
SELECT @Requete = @Requete +N' CROSS APPLY (SELECT NULL AS LocalReplicaRole) T5'
END
SELECT @Requete = @Requete +N' CROSS APPLY (SELECT @@SERVERNAME AS SERVERNAME) T6'
SELECT @Requete = @Requete +N' CROSS APPLY (SELECT TOP 1 REVERSE(SUBSTRING(REVERSE(physical_device_name),CHARINDEX(''''\'''',REVERSE(physical_device_name),1),LEN(physical_device_name))) AS backup_path FROM [msdb].[dbo].[backupmediafamily]
WHERE physical_device_name LIKE ''''%'+ @Backup_Abrg +'%'''' ) T7'
SELECT @Requete = @Requete +N' CROSS APPLY ('
+N'SELECT TOP 1 S1.[enabled] AS backup_job_enabled
FROM [msdb].[dbo].[sysjobs] S1
JOIN [msdb].[dbo].[sysjobsteps] S2 ON S2.job_id=S1.job_id
WHERE command LIKE ''''%'+ @Backup_Param +'%'''''
+N' ) T8'
-- Ecarter les bases avec le status
-- 66056 : Base déconnecté
-- 1073808384 , 1073807392 : snapshot
-- 66560 : Miroir Restauration
SELECT @Requete = @Requete +N' WHERE T1.[status] NOT IN (66056,1073808384,1073807392,66560) AND T1.[name] NOT IN (SELECT [database] FROM [dbo].[backup_database_exclure] WHERE '
+CASE @Backup_type WHEN'D'THEN'backup_full=1'
WHEN'L'THEN'backup_log=1'
WHEN'I'THEN'backup_diff=1'
END+')'
SELECT @Requete =CASEWHEN @LocalHot<>@Serveur_Lie THEN @Requete +N''')'ELSEREPLACE(@Requete,'''''','''')END
INSERTINTO #Resultat(Serveur_Lie,server_name,machine_name,[database_name],backup_job_enabled,backup_status,[type],backup_start_date,backup_finish_date,physical_device_name,backup_path,CommandBackup,LocalReplicaRole,backup_genere)
EXECUTEsp_executesql@Requete
END
ENDTRY
BEGINCATCH
SELECT @Serveur_Lie AS Serveur_Lie,ERROR_MESSAGE()AS Message_Erreur
INSERTINTO #Resultat(server_name,machine_name,backup_status,[type],backup_start_date)
VALUES (@Serveur_Lie,@Serveur_Lie,'KO',@Backup_type,@Date_Backup)
ENDCATCH
SELECT @Num_Lig = @Num_Lig + 1
END
UPDATE #Resultat
SET
Environnement=CASEWHEN physical_device_name LIKE'%BACKUP_PRODUCTION%'THEN'PRODUCTION'
WHEN physical_device_name LIKE'%BACKUP_HOMOLOGATION%'THEN'HOMOLOGATION'
WHEN physical_device_name LIKE'%BACKUP_DEVELOPPEMENT%'THEN'DEVELOPPEMENT'
ELSENULL
END
IFEXISTS(SELECT Num_Lig FROM #Resultat WHERE Environnement ISNULL)
BEGIN
--Trouver le chemin des sauvegardes
UPDATE #Resultat
SET
Environnement=CASEWHEN CommandBackup LIKE'%BACKUP_PRODUCTION%'THEN'PRODUCTION'
WHEN CommandBackup LIKE'%BACKUP_HOMOLOGATION%'THEN'HOMOLOGATION'
WHEN CommandBackup LIKE'%BACKUP_DEVELOPPEMENT%'THEN'DEVELOPPEMENT'
ELSE'INCONNU'
END
WHERE Environnement ISNULL
END
--Construire la requête de sauvegarde
UPDATE #Resultat
SET
RequeteBck='exec master.dbo.backup_base '''+ @Backup_Param +''', ''\\MesBackupSQLServer\BACKUP_'+ Environnement
+'\'','''+ [Database_name] +''';'
----Resultat brut
SELECT'Resultat brut'
SELECT RequeteBck,Environnement,server_name,machine_name,[database_name],backup_job_enabled,backup_status,[type],backup_start_date,backup_finish_date,physical_device_name,backup_path,LocalReplicaRole,backup_genere FROM #Resultat
WHERE server_name ISNOTNULLAND machine_name ISNOTNULL
ANDdatabase_nameNOTIN(SELECT Nom_Base FROM #DB_EXCLURE)
ORDERBY Environnement,server_name,[database_name]
--Supprimer la vérification des backups sur les serveurs Always-On qui ne sont pas des noeuds principaux
DELETEFROM #Resultat WHERE LocalReplicaRole<>1
--Supprimer les nom des backups qui ne sont pas conformes
DELETEFROM T1
FROM #Resultat T1
JOIN(
SELECT Environnement,server_name,machine_name,[database_name] FROM #Resultat WHERE physical_device_name LIKE'\\%.bak'OR physical_device_name LIKE'\\%.sqb'
) T2 ON T2.Environnement=T1.Environnement AND T2.server_name=T1.server_name AND T2.[database_name]=T1.[database_name]
WHEREISNULL(T1.backup_genere,0)<>1
UPDATE #Resultat
SET
backup_status='KO'
WHEREISNULL(backup_genere,0)<>1
--Resultat final
SELECT'Resultat final'
SELECT RequeteBck,Environnement,server_name,machine_name,[database_name],backup_job_enabled,backup_status,[type],backup_start_date,backup_finish_date,physical_device_name,backup_path FROM #Resultat
WHERE server_name ISNOTNULLAND machine_name ISNOTNULL
ANDdatabase_nameNOTIN(SELECT Nom_Base FROM #DB_EXCLURE)
ANDISNULL(backup_status,'')=CASE @backup_status WHEN'ALL'THENISNULL(backup_status,'')ELSE @backup_status END
ORDERBY Environnement,server_name,[database_name]
DROPTABLE #Serveurs_LiesTemp
DROPTABLE #Serveurs_Lies
DROPTABLE #DB_EXCLURE
DROPTABLE #Resultat
END
GO