[SQL Server] Afficher l'état de restauration des bases
-- ================================================
-- =============================================
-- Author: Zouhaier KHARROUBI
-- Create date: 20/09/2019
-- Description: Afficher l'état de restauration des bases
-- =============================================
/*Dans cette table on stocke les informations des bases à restaurer automatiquement*/
CREATETABLE [dbo].[bases_A_restaurer](
[num_restore] [bigint] IDENTITY(1,1)NOTNULL,
[active] [bit] NOTNULL,
[nom_job] [nvarchar](150)NOTNULL,
[job_id] [uniqueidentifier] NOTNULL,
[instance_source] [nvarchar](100)NOTNULL,
[database_source] [nvarchar](100)NOTNULL,
[instance_destination] [nvarchar](100)NOTNULL,
[database_destination] [nvarchar](100)NOTNULL,
[chemin_data] [nvarchar](500)NOTNULL,
[chemin_log] [nvarchar](500)NOTNULL,
[type_restore] [nvarchar](50)NULL,
[date_restore] [datetime] NULL,
[statut_restore] [nvarchar](500)NULL,
[statut_base] [nvarchar](50)NULL,
[commentaire] [nvarchar](300)NULL,
CONSTRAINT [PK_bases_restaurees] PRIMARY KEY CLUSTERED
(
[job_id] ASC,
[instance_source] ASC,
[database_source] ASC,
[instance_destination] ASC,
[database_destination] ASC
)WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON [PRIMARY]
)ON [PRIMARY]
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id =object_id(N'[tempdb]..[#Serveurs_Lies]'))
BEGIN
DROP TABLE #Serveurs_Lies
END
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id =object_id(N'[tempdb]..[#Resultat]'))
BEGIN
DROP TABLE #Resultat
END
CREATE TABLE #Serveurs_Lies
(
Num_Lig INTIDENTITY (1,1),
instance_source VARCHAR(150) NULL
,instance_destination VARCHAR(150) NULL
)
CREATE TABLE #Resultat
(
instance_source VARCHAR(150)NULL
,instance_destination VARCHAR(150)NULL
,nom_base NVARCHAR(128)NULL
,statut_base TINYINT NULL
,state_desc NVARCHAR(60)NULL
,restore_date DATETIME NULL
,restore_type CHAR(4) NULL
,[replace] BIT NULL
,[recovery] BIT NULL
,backup_start_date DATETIME NULL
,backup_finish_date DATETIME NULL
,[type] CHAR(1) NULL
,physical_device_name NVARCHAR(MAX)NULL
)
DECLARE @Num_Lig AS INT
,@Nbre_Lig AS INT
,@Requete AS NVARCHAR(MAX)
,@instance_source AS VARCHAR(150)
,@instance_destination AS VARCHAR(150)
,@LocalHot AS VARCHAR(50)
,@ListeBase AS NVARCHAR(MAX)='''''MaBase'''''
SELECT @LocalHot =QUOTENAME(@@SERVERNAME)
INSERT INTO #Serveurs_Lies(instance_source,instance_destination)
SELECT DISTINCT instance_source,instance_destination FROM [dbo].[bases_A_restaurer]
SELECT @Num_Lig=1,@Nbre_Lig=COUNT(Num_Lig) FROM #Serveurs_Lies
WHILE @Num_Lig <= @Nbre_Lig
BEGIN
BEGIN TRY
SELECT @instance_source=instance_source,@instance_destination=instance_destination FROM #Serveurs_Lies
WHERE Num_Lig=@Num_Lig
SELECT @ListeBase= @ListeBase +','''''+ database_destination +''''''FROM [dbo].[bases_restaurees]
WHERE instance_source=@instance_source AND instance_destination=@instance_destination
SELECT @Requete =CASE WHEN @LocalHot<>@instance_destination THEN N'SELECT * FROM OPENQUERY('+QUOTENAME(@instance_destination)+','''ELSE'' END
SELECT @Requete = @Requete +N'SELECT '''''+ @instance_source +''''' AS instance_source,'''''+ @instance_destination +''''' AS instance_destination'
+',T1.[name],T1.state,T1.state_desc,T2.restore_date
,restore_type = CASE T2.restore_type WHEN ''''D'''' THEN ''''FULL''''
WHEN ''''I'''' THEN ''''DIFF''''
WHEN ''''L'''' THEN ''''LOG''''
ELSE NULL END
,T2.[replace],T2.[recovery]
,T3.backup_start_date,T3.backup_finish_date,T3.[type],T4.physical_device_name
FROM [msdb].sys.databases T1
LEFT JOIN [msdb]..restorehistory T2 ON T2.destination_database_name=T1.[name] AND CONVERT(VARCHAR(10), T2.restore_date,112)=CONVERT(VARCHAR(10), GETDATE(),112)
LEFT JOIN [msdb].[dbo].[backupset] T3 ON T3.backup_set_id=T2.backup_set_id
LEFT JOIN [msdb].[dbo].[backupmediafamily] T4 ON T4.media_set_id=T3.media_set_id
WHERE T1.[name] IN ('+ @ListeBase +N')
ORDER BY T1.[name],T2.restore_date'
SELECT @Requete =CASE WHEN @LocalHot<>@instance_destination THEN @Requete +N''')' ELSE REPLACE(@Requete,'''''','''') END
INSERT INTO #Resultat(instance_source,instance_destination,nom_base,statut_base,state_desc,restore_date,restore_type
,[replace],[recovery],backup_start_date,backup_finish_date,[type],physical_device_name)
EXECUTE sp_executesql @Requete
END TRY
BEGIN CATCH
SELECT @instance_source AS instance_source,@instance_destination AS instance_destination,ERROR_MESSAGE()AS Message_Erreur
END CATCH
SELECT @Num_Lig = @Num_Lig + 1
END
SELECT * FROM #Resultat ORDER BY restore_date,instance_source,instance_destination,nom_base
DROP TABLE #Serveurs_Lies
DROP TABLE #Resultat