Liste des bases en off

**agdid04** · 28/06/2018, 16h47

Bonjour,

Je cherche à récupérer la liste de toutes les bases détachées d'une instance via les vues système ?

MERCI.

A+

**OokieDookie91** · 28/06/2018, 17h53

Bonjour,

Une bdd détachée n'est plus dans l'instance. Si on parle de bases OFFLINE en revanche, il faut utiliser sys.databases :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT
		name
	,	database_id
	,	state_desc
	,	state
FROM	
		master.sys.databases
WHERE
		state = 6
ORDER BY 
		name

HTH,

**janlouk** · 28/06/2018, 20h41

Si tu veux trouver les DB qui ont été détachée et dont les fichiers sont encore sur le disque, tu as ceci fait par Hamid.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
--https://www.developpez.com/actu/110283/SQL-Server-moins-Apprendre-une-methode-pour-lister-les-fichiers-de-donnees-mdf-ndf-et-de-log-ldf-orphelins-un-tutoriel-de-Hmira/
 
-- J'utilise la base master, mais vous pouvez utiliser une autre base de votre choix 
USE master 
GO 
IF NOT EXISTS ( SELECT p.* 
				FROM sys.procedures AS p WITH (nolock) 
				INNER JOIN sys.schemas AS s WITH (nolock) 
				  ON p.schema_id = p.schema_id 
				WHERE s.name = N'dbo' AND P.name = N'uspListeFichiersDataOuLogOrphelinsDeBasesDeDonnees' AND P.type IN (N'P', N'PC') 
			  ) 
BEGIN 
	EXEC dbo.sp_executesql 
		 @statement = N'CREATE PROCEDURE dbo.uspListeFichiersDataOuLogOrphelinsDeBasesDeDonnees 
AS 
BEGIN 
-- !!! "Stub" doit être implémenté !!! 
SET NOCOUNT ON; 
END; '; 
END; 
GO 
 
-- ---------------------------------------------------------------------------- 
-- Procédure          : dbo.uspListeFichiersDataOuLogOrphelinsDeBasesDeDonnees 
-- Créateur           : Hamid MIRA 
-- Date de création   : 28/12/2016 
-- Objet              : Ce script permet de lister les fichiers de données (.mdf, .ndf, etc.) 
--                      ou les fichiers de journaux de transactions (.ldf) orphelins, c.à.d. des fichiers (.mdf, .ndf, ldf , etc.) 
--                      qui ne sont rattachés à aucune base de données du Serveur. 
-- Paramètres : 
--   @pi_DefaultDirectoryData : Le répertoire explicite des fichiers de données (.mdf, ndf)  Exemple : N'C:\SQL\Data' 
--                              Transmettez NULL pour utiliser le répertoire par défaut des fichiers de données défini au niveau de l'instance 
--   @pi_DefaultDirectoryLog  : Le répertoire explicite des fichiers des journaux de transaction  (.ldf) )  Exemple : 'C:\SQL\Log' 
--                              Transmettez NULL pour utiliser le répertoire par défaut des fichiers des journaux de transactions défini au niveau de l'instance 
-- Exemple d'utilisation : 
--   EXEC dbo.uspListeFichiersDataOuLogOrphelinsDeBasesDeDonnees NULL, NULL 
--   EXEC dbo.uspListeFichiersDataOuLogOrphelinsDeBasesDeDonnees N'C:\SQL\Data', N'C:\SQL\Log' 
-- ---------------------------------------------------------------------------- 
ALTER PROCEDURE dbo.uspListeFichiersDataOuLogOrphelinsDeBasesDeDonnees 
(@pi_DefaultDirectoryData nvarchar(512) = NULL, 
 @pi_DefaultDirectoryLog  nvarchar(512) = NULL  ) 
AS 
BEGIN 
	SET NOCOUNT ON 
	DECLARE @MasterDirectoryData nvarchar(512), 
			@MasterDirectoryLog nvarchar(512),  
			@VersionMajor tinyint; 
 
	SET @VersionMajor = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(128)), CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(128))) - 1) AS INT); 
	PRINT '@VersionMajor=['+CAST(ISNULL(@VersionMajor,-1) AS VARCHAR(2))+']'; 
 
	IF @pi_DefaultDirectoryData IS NULL 
	BEGIN 
		-- Version SQL Server 2008 R2 (incluse) et versions antérieures 
		IF @VersionMajor <= 10 
		BEGIN 
			EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @pi_DefaultDirectoryData output 
		END 
		ELSE 
		BEGIN 
			-- Version SQL Server 2012 (incluse) et versions supérieures 
			SET @pi_DefaultDirectoryData =	CAST(SERVERPROPERTY('INSTANCEDEFAULTDATAPATH') AS nvarchar(512)); 
		END; 
		IF @pi_DefaultDirectoryData IS NULL 
		BEGIN 
			EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters', N'SqlArg0', @MasterDirectoryData output 
			SET @MasterDirectoryData = SUBSTRING(@MasterDirectoryData, 3, 255) 
			SET @MasterDirectoryData = SUBSTRING(@MasterDirectoryData, 1, LEN(@MasterDirectoryData) - CHARINDEX('\', REVERSE(@MasterDirectoryData))) 
			SET @pi_DefaultDirectoryData = @MasterDirectoryData; 
		END; 
	END; 
 
	IF @pi_DefaultDirectoryLog IS NULL 
	BEGIN 
		-- Version SQL Server 2008 R2 (incluse) et versions antérieures 
		IF @VersionMajor <= 10 
		BEGIN 
			EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @pi_DefaultDirectoryLog output 
		END 
		ELSE 
		BEGIN 
			-- Version SQL Server 2012 (incluse) et versions supérieures 
			SET @pi_DefaultDirectoryLog =  CAST( SERVERPROPERTY('INSTANCEDEFAULTLOGPATH') AS nvarchar(512)); 
		END; 
 
		IF @pi_DefaultDirectoryLog IS NULL 
		BEGIN 
			EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters', N'SqlArg2', @MasterDirectoryLog output 
			SET @MasterDirectoryLog = SUBSTRING(@MasterDirectoryLog, 3, 255) 
			SET @MasterDirectoryLog = SUBSTRING(@MasterDirectoryLog, 1, LEN(@MasterDirectoryLog) - CHARINDEX('\', REVERSE(@MasterDirectoryLog))) 
			SET @pi_DefaultDirectoryLog = @MasterDirectoryLog; 
		END; 
	END; 
 
	-- Recadrage éventuel de la valeur @pi_DefaultDirectoryData 
	SET @pi_DefaultDirectoryData = RTRIM(LTRIM((@pi_DefaultDirectoryData))); 
	SET @pi_DefaultDirectoryData  = LEFT(@pi_DefaultDirectoryData, 2) + REPLACE (RIGHT(@pi_DefaultDirectoryData, len(@pi_DefaultDirectoryData) -2), '\\', '\'); 
	IF RIGHT(@pi_DefaultDirectoryData, 1) <> '\' 
		SET @pi_DefaultDirectoryData = @pi_DefaultDirectoryData + '\'; 
 
    -- Recadrage éventuel de la valur @pi_DefaultDirectoryLog 
	SET @pi_DefaultDirectoryLog = RTRIM(LTRIM(( @pi_DefaultDirectoryLog))); 
	SET @pi_DefaultDirectoryLog =  LEFT(@pi_DefaultDirectoryLog, 2) + REPLACE (RIGHT(@pi_DefaultDirectoryLog, len(@pi_DefaultDirectoryLog) -2), '\\', '\'); 
	IF RIGHT( @pi_DefaultDirectoryLog, 1) <> '\' 
		SET @pi_DefaultDirectoryLog = @pi_DefaultDirectoryLog + '\'; 
 
	PRINT '@pi_DefaultDirectoryData=[' +ISNULL(@pi_DefaultDirectoryData, '{NULL}') +']'; 
	PRINT '@pi_DefaultDirectoryLog=[' +ISNULL( @pi_DefaultDirectoryLog, '{NULL}') +']'; 
 
	IF OBJECT_ID('tempdb.dbo.#DirTreeData') IS NOT NULL 
		DROP TABLE #DirTreeData 
	IF OBJECT_ID('tempdb.dbo.#DirTreeLog') IS NOT NULL 
		DROP TABLE #DirTreeLog 
 
	-- ----------------------------------------- 
	CREATE TABLE #DirTreeData( 
		Id int identity(1,1) PRIMARY KEY CLUSTERED, 
		SubDirectory nvarchar(255), 
		Depth smallint, 
		FileFlag bit, 
		ParentDirectoryID int 
		); 
	CREATE TABLE #DirTreeLog( 
		Id int identity(1,1) PRIMARY KEY CLUSTERED, 
		SubDirectory nvarchar(255), 
		Depth smallint, 
		FileFlag bit, 
		ParentDirectoryID int 
		); 
 
	INSERT INTO #DirTreeData (SubDirectory, Depth, FileFlag) 
	EXEC master..xp_dirtree @pi_DefaultDirectoryData, 1, 1; -- Profondeur : 1 (premier niveau), Lister également les fichiers : 1  (Oui) 
 
	UPDATE #DirTreeData 
		SET SubDirectory = @pi_DefaultDirectoryData + LTRIM(RTRIM(SubDirectory)); 
 
	IF ISNULL(@pi_DefaultDirectoryLog, '') <> ISNULL(@pi_DefaultDirectoryData, '') 
	BEGIN 
		INSERT INTO #DirTreeLog(SubDirectory, Depth, FileFlag) 
		EXEC master..xp_dirtree  @pi_DefaultDirectoryLog, 1, 1; -- Profondeur : 1 (premier niveau), lister également les fichiers : 1  (Oui) 
		UPDATE #DirTreeLog 
			SET SubDirectory = @pi_DefaultDirectoryLog + LTRIM(RTRIM(SubDirectory)); 
	END 
 
	;WITH smf AS 
	  (SELECT LEFT(RTRIM(LTRIM(physical_name)), 2) + REPLACE (RIGHT(RTRIM(LTRIM(physical_name)), len(RTRIM(LTRIM(physical_name))) -2), '\\', '\') AS physical_name_fmt 
       FROM master.sys.master_files WITH (NOLOCK) ) 
 
	SELECT SubDirectory 
	FROM #DirTreeData  dtd 
	LEFT OUTER JOIN smf 
	   ON smf.physical_name_fmt = dtd.SubDirectory 
	WHERE dtd.FileFlag = 1            -- Fichiers uniquement (ignorer les répertoires) 
	AND smf.physical_name_fmt IS NULL -- Pas de correspondance du fichier SubDirectory dans master.sys.master_files 
	UNION ALL 
	SELECT SubDirectory 
	FROM #DirTreeLog dtl 
	LEFT OUTER JOIN smf 
	   ON smf.physical_name_fmt = dtl.SubDirectory 
	WHERE dtl.FileFlag = 1            -- Fichier uniquement (ignorer les répertoires) 
	AND smf.physical_name_fmt IS NULL --  Pas de correspondance du fichier SubDirectory dans master.sys.master_files 
	ORDER BY SubDirectory;  
 
	-- Suppression des tables temporaires  
	IF OBJECT_ID('tempdb.dbo.#DirTreeData') IS NOT NULL 
		DROP TABLE #DirTreeData 
	IF OBJECT_ID('tempdb.dbo.#DirTreeLog') IS NOT NULL 
		DROP TABLE #DirTreeLog 
END; 
GO

**agdid04** · 29/06/2018, 11h38

Bonjour,

Merci aux 2 intervenants, je vais essayer la procédure de Hamid ....

A+

Invité · 29/06/2018, 14h48

Envoyé par janlouk

Si tu veux trouver les DB qui ont été détachée et dont les fichiers sont encore sur le disque, tu as ceci fait par Hamid.

Si je comprends bien, ça fait juste comparer les fichiers dans les répertoires bd par défaut (ainsi que les sous répertoires) avec les datafiles actifs...
Mettons que ça donne une idée très approximatives parce que si les fichiers sont placés ailleurs, c'est mort !

**agdid04** · 29/06/2018, 17h27

Bonjour,

Tu fixes ton répertoire de recherche à chaque fois.

A+

**hmira** · 29/06/2018, 23h56

Envoyé par agdid04

Bonjour,

Merci aux 2 intervenants, je vais essayer la procédure de Hamid ....

A+

Bonjour,

Une précision concernant mon prénom.
Le H d’Hamid est un "h muet" ! Même si Hamid peut, parfois, être bavard

Le H d’Hamid n’est pas un "h aspiré" ; il n’empêche donc ni l’élision ni la liaison.
On dira par exemple :
"La pensée d’Hamid" et non pas "La pensée de Hamid",
"L’idée qu’Hamid a suggérée",
Etc.

Si cette règle de grammaire n'est pas respectée, je vais peut être déterrer le H de guerre

PS : Il s’agit d’une note d’humour.

A+

**hmira** · 30/06/2018, 00h04

Envoyé par agdid04

Bonjour,

Tu fixes ton répertoire de recherche à chaque fois.

A+

Oui tout à fait.

La procédure n'est pas prévue pour scruter l'intégralité d'un disque dur !

A+

**agdid04** · 30/06/2018, 17h58

Envoyé par hmira

Bonjour,

Une précision concernant mon prénom.
Le H d’Hamid est un "h muet" ! Même si Hamid peut, parfois, être bavard

Le H d’Hamid n’est pas un "h aspiré" ; il n’empêche donc ni l’élision ni la liaison.
On dira par exemple :
"La pensée d’Hamid" et non pas "La pensée de Hamid",
"L’idée qu’Hamid a suggérée",
Etc.

Si cette règle de grammaire n'est pas respectée, je vais peut être déterrer le H de guerre

PS : Il s’agit d’une note d’humour.

A+

Sorry Si Hamid en tout cas MERCI de ta proc.

A+

**dari68** · 05/07/2018, 16h23

Bonjour,

Je relance la conversation, je cherche la date de la dernière rattachement d'une base ?

MERCI

Invité · 05/07/2018, 16h35

j'ai fait un rapide test sur une instance 2012 édition Dev et j'ai constaté qu'au moment du détachement, les fichiers log de la bd prenait la date du détachement.
Sans doute la cloture des transactions système.
À confirmer avec des tests un peu plus poussés mais c'est une première piste.

**dari68** · 05/07/2018, 17h25

Merci tu parles de détachement ou rattachement ?

Je ne sais pas s'il y des vues dans master ou msdb pour ce type d'infos ...

A+

Invité · 05/07/2018, 17h34

Je parle bien de détachement, je n'ai pas été clair ?
De mémoire, lorsque une bd est détachée, il y a suppression de ces données dans l'historique des tables système.
C'est possible qu'il reste cependant quelques traces dans certaines tables systèmes, SQL Server n'est pas toujours très propre dans ses purges de tables système.
Mais là encore, je ne me fierais pas totalement à la fiabilité de ces potentielles reliquats de données.

Mais pour reprendre ton problème, pourquoi as-tu besoin de ces données ? En en sachant plus, on peut peut-être t'aider autrement...

**dari68** · 05/07/2018, 18h19

J'ai une base rattachée dernièrement et je voudrais savoir quand est ce qu'elle l'est devenue ?

MERCI

A+

Invité · 06/07/2018, 21h09

Envoyé par dari68

J'ai une base rattachée dernièrement et je voudrais savoir quand est ce qu'elle l'est devenue ?

Un rapide test sur un 2008R2 laisserait penser que la creation_date dans sys.databases donne cette valeur... C'est une piste À valider un peu plus profondément.

Liste des bases en off

Administration SQL Server

Discussions similaires

Partager

Partager