[SQL Server] Afficher le chemin de sauvegarde pour une instance SQL Server
-- =============================================
-- Author: Zouhaier KHARROUBI
-- Create date: 17/02/2019
-- Description: Afficher le chemin de sauvegarde d’une base de données sur instance SQL Server
-- =============================================
-- Add the parameters for the stored procedure here
DECLARE @NomServeur VARCHAR(100)
,@NomBase VARCHAR(150)=NULL
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SETNOCOUNTON;
IFEXISTS(SELECT*FROM tempdb..sysobjectsWHERE id =object_id(N'[tempdb]..[#Resultat]'))
BEGIN
DROPTABLE #Resultat
END
CREATETABLE #Resultat
(
NomServeur VARCHAR(100) NULL,
NomBase VARCHAR(100) NULL,
Path_Backup VARCHAR(1500)NULL
)
-- Insert statements for procedure here
DECLARE @Requete ASNVARCHAR(MAX)
,@LocalHot ASVARCHAR(50)
SELECT @LocalHot =@@SERVERNAME
IFEXISTS(SELECT [name] FROM [master].[sys].[servers] WHERE [data_source]=@NomServeur)
BEGIN
SELECT @Requete =N' SELECT NomServeur,'''+ISNULL(CAST(@NomBase ASVARCHAR(150)),'ALL')+''' AS NomBase,backup_path FROM OPENQUERY('+QUOTENAME(@NomServeur)+N',''SELECT TOP 1 @@SERVERNAME AS NomServeur,REVERSE(SUBSTRING(REVERSE(physical_device_name),CHARINDEX(''''\'''',REVERSE(physical_device_name),1),LEN(physical_device_name))) AS backup_path FROM [msdb].[dbo].[backupmediafamily]'
IF @NomBase ISNOTNULL
BEGIN
SELECT @Requete = @Requete +N' WHERE physical_device_name LIKE ''''%'+CAST(@NomBase ASVARCHAR(150))+'%'''''
END
SELECT @Requete = @Requete +''')'
INSERTINTO #Resultat(NomServeur,NomBase,Path_Backup)
EXECUTEsp_executesql@Requete
UPDATE #Resultat
SET
Path_Backup =SUBSTRING(Path_Backup,1,LEN(Path_Backup)-1)
UPDATE #Resultat
SET
Path_Backup =REVERSE(SUBSTRING(REVERSE(Path_Backup),CHARINDEX('\',REVERSE(Path_Backup),1),LEN(Path_Backup)))
IF @NomBase ISNULL
BEGIN
UPDATE #Resultat
SET
Path_Backup =SUBSTRING(Path_Backup,1,LEN(Path_Backup)-1)
UPDATE #Resultat
SET
Path_Backup =REVERSE(SUBSTRING(REVERSE(Path_Backup),CHARINDEX('\',REVERSE(Path_Backup),1),LEN(Path_Backup)))
END
SELECT*FROM #Resultat
END
ELSE
BEGIN
SELECT'L''instance '+QUOTENAME(@NomServeur)+' n''existe pas !'AS Erreur
END
END