curseur pour backup [Résolu]

[ldiaz]

SQL-server 2055

Bonjour a tous,
je souhaite executer cette commande mais avec un curseur pour que toutes mes bases soient sauvegardées:

Code :

1
2
3
USE TfsVersionControl
BACKUP LOG TfsVersionControl TO DISK='S:\Temp\TfsVersionControlLog.bak'
DBCC SHRINKFILE (TfsVersionControl_log, 1) WITH NO_INFOMSGS

Donc pour ce faire, j'ai essayer ce script, mais ça donne une erreur de sintaxe:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DECLARE @name NVARCHAR(MAX) -- Nombre de la base de datos 
DECLARE db_cursor CURSOR FOR 
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb') 
 
OPEN db_cursor  NVARCHAR(100)
FETCH NEXT FROM db_cursor INTO @name  
 
BACKUP LOG 'nombre'+@name+'_log' TO DISK='S:\Temp\' + @name + '.bak'
DBCC SHRINKFILE ('nombre'+@name+'_log' , 1) WITH NO_INFOMSGS
 
CLOSE db_cursor  
DEALLOCATE db_cursor

Quelqu'in voit ou se trouve cette erreur?
D'avance merci

[SQLpro]

Vous n'avez pas fermé correctement une chaine de caractères.

a +

[elsuket]

Bonjour,

Malheureusement, il n'y a pas que cela :

- On ne peut pas paramétrer une opération de sauvegarde comme vous le faites. Il faut pour cela utiliser du code SQL dynamique
- l'instruction est BACKUP LOG <maBD>, jamais rien d'autre.
- Vous ne passez pas à la BD suivante !

En outre :

- Vous ne vérifiez pas que la base de données est en ligne
- Vous ne vérifiez pas que la base de données n'est pas une capture instantanée
- le nom d'un objet (base de données, table, vue, trigger, procédure, fonction, index, contrainte, rôle, login, utilisateur, ... ) et tous les noms logiques (de fichier, de serveur, ...) est de type sysname qui est en fait un nvarchar(128), donc il est inutile d'utiliser un nvarchar(max).
- USE est inutile.

Cela donne donc le code suivant :

Code :

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
DECLARE @database_name sysname -- Nombre de la base de datos
	, @logical_transaction_log_file_name sysname
	, @sql nvarchar(max)
 
DECLARE db_cursor CURSOR LOCAL FAST_FORWARD FOR 
	SELECT		D.name AS database_name
			, MF.name AS logical_transaction_log_file_name
	FROM		sys.DATABASES AS D
	INNER JOIN	sys.master_files AS MF
				ON D.database_id = MF.database_id
	WHERE		D.name NOT IN ('master','model','msdb','tempdb')
	AND		D.state_desc = 'ONLINE' -- la base de données n'est pas OFFLINE ou ne cours de restauration
	AND		D.source_database_id IS NULL -- la base de données n'est pas une capture instantanée
	AND		MF.file_id = 2 -- transaction log
 
OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @database_name, @logical_transaction_log_file_name
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @sql = 'BACKUP LOG ' + @database_name
		+ 'TO DISK = S:\Temp\nombre_' + @database_name + '_log.bak'
 
	PRINT @sql
	EXEC (@sql)
 
	SET @sql = 'DBCC SHRINKFILE (' + @logical_transaction_log_file_name + ', 1) WITH NO_INFOMSGS'
	PRINT @sql
	EXEC (@sql)
 
	FETCH NEXT FROM db_cursor INTO @database_name, @logical_transaction_log_file_name
END	 
CLOSE db_cursor  
DEALLOCATE db_cursor

Enfin, faire rétrécir un fichier est inutile, de surcroit lorsqu'il s'agit d'une des bases très importantes de TFS !
Cet espace sera repris tôt ou tard par la base de données, et les transactions attendront que le fichier ait finit de grossir pour se terminer ...
Je plains les utilisateurs, surtout si vous avez mis un incrément de fichier petit et que TFS est massivement utilisé
Et pour fragmenter le fichier, c'est le top !
Faites donc des sauvegardes plus fréquentes de vos fichiers du journal des transactions

Le rétrécissement de fichier est une manœuvre d'urgence par pur manque d'espace disque, ce que ne semble pas suggérer l'implémentation d'un tel code.

@++

[ldiaz]

Bonjour,

merci pour ta reponse, le probleme c'est ça:
Je dois specifier que certains nom de base sont de type:

nom-nom_nom
Comme clui ci par exemple:

Code :

1
2
3
ePO4_ATC-ANTIVIRUS	
Insight_v50_0_141316462	
SharePoint_AdminContent_eb921f48-367e-4f62-945f-66d33a24e312

Donc j'ai googler, et je vois qu'il faut ajouter des [] pour que le script lise le nom sans s'emeller.

Voici le resultat sans les []

Code :

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
BACKUP LOG bbdd_docatcTO DISK = S:\nombre_bbdd_docatc_log.bak
Mens. 102, Nivel 15, Estado 1, Línea 1
Sintaxis incorrecta cerca de 'DISK'.
DBCC SHRINKFILE (bbdd_docatc_log, 1) WITH NO_INFOMSGS
Mens. 8985, Nivel 16, Estado 1, Línea 1
No se encontró el archivo 'bbdd_docatc_log' para la base de datos 'master'.
BACKUP LOG ePO4_ATC-ANTIVIRUSTO DISK = S:\nombre_ePO4_ATC-ANTIVIRUS_log.bak
Mens. 102, Nivel 15, Estado 1, Línea 1
Sintaxis incorrecta cerca de '-'.
DBCC SHRINKFILE (ePO4_ATC-ANTIVIRUS_log, 1) WITH NO_INFOMSGS
Mens. 102, Nivel 15, Estado 1, Línea 1
Sintaxis incorrecta cerca de '-'.
Mens. 319, Nivel 15, Estado 1, Línea 1
Sintaxis incorrecta junto a la palabra clave 'with'. Si esta instrucción es una expresión de tabla común o una cláusula xmlnamespaces, la instrucción anterior debe terminarse con punto y coma (;).
BACKUP LOG imsaTO DISK = S:\nombre_imsa_log.bak
Mens. 102, Nivel 15, Estado 1, Línea 1
Sintaxis incorrecta cerca de 'DISK'.
DBCC SHRINKFILE (ATCimsa_log, 1) WITH NO_INFOMSGS
Mens. 8985, Nivel 16, Estado 1, Línea 1
No se encontró el archivo 'ATCimsa_log' para la base de datos 'master'.
BACKUP LOG Insight_v50_0_141316462TO DISK = S:\nombre_Insight_v50_0_141316462_log.bak
Mens. 102, Nivel 15, Estado 1, Línea 1
Sintaxis incorrecta cerca de 'DISK'.
DBCC SHRINKFILE (Insight_v50_0_141316462_log, 1) WITH NO_INFOMSGS
Mens. 8985, Nivel 16, Estado 1, Línea 1
No se encontró el archivo 'Insight_v50_0_141316462_log' para la base de datos 'master'.
BACKUP LOG SharePoint_AdminContent_eb921f48-367e-4f62-945f-66d33a24e312TO DISK = S:\nombre_SharePoint_AdminContent_eb921f48-367e-4f62-945f-66d33a24e312_log.bak
Mens. 102, Nivel 15, Estado 1, Línea 1
Sintaxis incorrecta cerca de '-'.
DBCC SHRINKFILE (SharePoint_AdminContent_eb921f48-367e-4f62-945f-66d33a24e312_log, 1) WITH NO_INFOMSGS
Mens. 102, Nivel 15, Estado 1, Línea 1
Sintaxis incorrecta cerca de '-'.
Mens. 319, Nivel 15, Estado 1, Línea 1
Sintaxis incorrecta junto a la palabra clave 'with'. Si esta instrucción es una expresión de tabla común o una cláusula xmlnamespaces, la instrucción anterior debe terminarse con punto y coma (;).
BACKUP LOG SharePoint_Config_IntranetTO DISK = S:\nombre_SharePoint_Config_Intranet_log.bak
Mens. 102, Nivel 15, Estado 1, Línea 1
Sintaxis incorrecta cerca de 'DISK'.
DBCC SHRINKFILE (SharePoint_Config_Intranet_log, 1) WITH NO_INFOMSGS
Mens. 8985, Nivel 16, Estado 1, Línea 1
No se encontró el archivo 'SharePoint_Config_Intranet_log' para la base de datos 'master'.
BACKUP LOG SUSDBTO DISK = S:\nombre_SUSDB_log.bak
Mens. 102, Nivel 15, Estado 1, Línea 1
Sintaxis incorrecta cerca de 'DISK'.
DBCC SHRINKFILE (SUSDB_log, 1) WITH NO_INFOMSGS
Mens. 8985, Nivel 16, Estado 1, Línea 1
No se encontró el archivo 'SUSDB_log' para la base de datos 'master'.
BACKUP LOG WSS_Search_ATCBCNAPPSTO DISK = S:\nombre_WSS_Search_ATCBCNAPPS_log.bak
Mens. 102, Nivel 15, Estado 1, Línea 1
Sintaxis incorrecta cerca de 'DISK'.
DBCC SHRINKFILE (WSS_Search_ATCBCNAPPS_log, 1) WITH NO_INFOMSGS
Mens. 8985, Nivel 16, Estado 1, Línea 1
No se encontró el archivo 'WSS_Search_ATCBCNAPPS_log' para la base de datos 'master'.

et voici le resultat avec les []

Code :

1
2
3
4
Mens. 207, Nivel 16, Estado 1, Línea 20
El nombre de columna '@database_name' no es válido.
Mens. 207, Nivel 16, Estado 1, Línea 20
El nombre de columna '@database_name' no es válido.

ça dit: le nom de la colonne @database_name n'est pas valide

[SQLpro]

Beaucoup d'horreur dans ce code :
1) utilisation inutile de curseur
2) supposition fausse qu'un fichier de JT a un ID de 2
3) supposition fausse qu'il n'y a qu'un seul fichier pour le JT

Une solution :

Code :

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
DECLARE @SQL nvarchar(max);
SET @SQL = '';
 
WITH T AS
(SELECT 'BACKUP LOG [' + name +'] TO DISK = ''S:\Temp\nombre_' 
        + REPLACE(REPLACE(REPLACE(name, '-', ''), ' ', ''), '/', '') 
        + '_log.bak'''  AS COMMANDE_SQL
 FROM   sys.DATABASES
 WHERE	name NOT IN ('master','model','msdb','tempdb')
   AND  state_desc = 'ONLINE' -- la base de données n'est pas OFFLINE ou ne cours de restauration
   AND  source_database_id IS NULL -- la base de données n'est pas une capture instantanée
 UNION ALL        
 SELECT 'DBCC SHRINKFILE (''' + mf.name + ''', 1) WITH NO_INFOMSGS;'
 FROM   sys.master_files AS mf
        INNER JOIN sys.DATABASES AS d
              ON d.database_id = mf.database_id
 WHERE  d.name NOT IN ('master','model','msdb','tempdb')
   AND  d.state_desc = 'ONLINE' -- la base de données n'est pas OFFLINE ou ne cours de restauration
   AND  d.source_database_id IS NULL -- la base de données n'est pas une capture instantanée
   AND	mf."type" = 1 -- transaction log
)
SELECT @SQL = @SQL + COMMANDE_SQL +';'
FROM   T;
 
EXEC (@SQL)

A +

[elsuket]

Effectivement, c'est sympa de l'avoir relevé

Nous avons aussi oublié que l'on ne peut effectuer une sauvegarde du / des fichier(s) du journal des transactions que lorsque le mode de récupération de la base de données n'est pas SIMPLE.

Enfin si l'on a copié la base de données dans la même instance sans renommer les fichiers logique, le DBCC SHRINKFILE pose problème, d'autant qu'il ne s'effectue que pour la base de données en cours.

On a donc :

Code :

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
DECLARE @sql nvarchar(max)
 
SELECT @sql = CASE WHEN  @sql IS NULL THEN sql_string ELSE @sql + sql_string END
FROM	(
 
			SELECT	'BACKUP LOG ' + QUOTENAME(name)
				+ ' TO DISK = ''S:\Temp\nombre_' +
				+ REPLACE(REPLACE(REPLACE(name, '-', ''), ' ', ''), '/', '') + '_log.bak'';' AS sql_string
			FROM	sys.DATABASES
			WHERE	name NOT IN ('master','model','msdb','tempdb', 'distribution', 'ReportServer', 'ReportServerTempDB')
			AND	state_desc = 'ONLINE' -- la base de données n'est pas OFFLINE ou en cours de restauration/récupération
			AND	source_database_id IS NULL -- la base de données n'est pas une capture instantanée
			AND	recovery_model_desc <> 'SIMPLE' -- le(s) fichier(s) du journal des transactions ne peut être sauvegardé que lorsque le mode de récupération de la base de données est FULL ou BULK_LOGGED
		UNION ALL
			SELECT		'USE ' + D.name + ';' +
					'DBCC SHRINKFILE(''' + MF.name + ''', 1)' + ';'
			FROM		sys.DATABASES AS D
			INNER JOIN	sys.master_files AS MF ON D.database_id = MF.database_id
			WHERE		D.name NOT IN ('master','model','msdb','tempdb', 'distribution', 'ReportServer', 'ReportServerTempDB')
			AND		D.state_desc = 'ONLINE' -- la base de données n'est pas OFFLINE ou en cours de restauration/récupération
			AND		D.source_database_id IS NULL -- la base de données n'est pas une capture instantanée
			AND		MF.type_desc = 'LOG'
	) AS Q
 
EXEC	(@sql)

@++

[ldiaz]

Salut a tous
ok les 2 deniers scripts marche bien.
Merci encore a tous pour votre aide.

Ciao et vive l'ete !!