Script de sauvegardes

Version imprimable

Bonjour,

j'essaie de sauvegarder les bases de données hébergées par un serveur SQL Server 2008 (SP3) vers un espace de stockage situé sur un serveur Windows 2012 distant.

J'ai récupéré des scripts sur différents sites et j'ai finalement ce script:
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 DECLARE @name VARCHAR(50) -- database name DECLARE @path VARCHAR(256) -- path for backup files DECLARE @fileName VARCHAR(256) -- filename for backup DECLARE @fileDate VARCHAR(20) -- used for file name -- specify database backup directory SET @path = '\\serveurdistant\backupdir' -- specify filename format SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) -- delete backups older than 6 days EXEC xp_cmdshell 'FORFILES /p 'N:' /s /m *.bak /d -6 /c "CMD /C del /Q /F @FILE"' DECLARE db_cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('model','tempdb') -- exclude these databases OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + '_' + @name + '_' + @fileDate + '.BAK' PRINT 'BACKUP DATABASE @name TO DISK = [' + @filename + ']' FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor
( Au départ j'avais des problèmes de droits d'accès au répertoire distant que j'ai réglé en mettant l'administrateur du domaine pour le lancement de SQL Server.
Puis avec le @filename que j'ai remplacé par [' + @filename + ']'... Ça marche mais je ne comprends rien à cette syntaxe... )
J'utilise maintenant N: au lieu du nom UNC car FORFILES me rendait une erreur comme quoi il ne gère pas les chemins UNC.
J'ai un problème du même genre maintenant et j'obtiens ce message d'erreur:

Code:

Syntaxe incorrecte vers ' /s /m *.bak /d -6 /c "CMD /C del /Q /F @FILE"'

J'enlève, j'ajoute des ', des ", des [ etc. mais rien n'y fait... Merci pour votre aide!

13/09/2016, 12h09
al1_24

Le problème semble se situer dans cette ligne :

Code:

EXEC xp_cmdshell 'FORFILES /p 'N:' /s /m *.bak /d -6 /c "CMD /C del /Q /F @FILE"'

Et plus précisément derrière N:.
Je ne connais pas la commande que tu cherches à exécuter mais il y a de fortes chances que celle-ci attende que N: soit encadré de guillemets, ce qui n'est pas le cas ici puisque lesdits guillemets sont utilisés pour encadrer la commande...
Essaye peu-être en échappant les guillemets autour du N: :

Code:

EXEC xp_cmdshell 'FORFILES /p \'N:\' /s /m *.bak /d -6 /c "CMD /C del /Q /F @FILE"'
13/09/2016, 13h05
nutopia

Merci pour ton aide.
Avec ta modification, j'ai le même genre d'erreur:

Code:

Syntaxe incorrecte vers '\'.

Je cherche à supprimer les anciens fichiers de sauvegarde. Mais il y a peut-être plus simple? Un argument dans BACKUP etc.
13/09/2016, 13h28
nutopia

Si j'enlève les guillemets autour de N: alors le message d'erreur est que le chemin spécifié n'existe pas.
Si (toujours sans les guillemets) je remplace N par un lecteur local alors ça fonctionne...

Je ne peux ni utiliser de chemin UNC, ni de lettre de lecteur réseaux? Sans ça, je ne vois pas comment faire pour atteindre le répertoire distant...
13/09/2016, 13h37
nutopia

Je me suis débrouillé avec un "WITH INIT" en argument de BACKUP.
Je laisse ouvert au cas où quelqu'un peut expliquer pour la solution avec FORFILES.

Merci!
13/09/2016, 13h53
nutopia
En fait ça ne marche pas...
Toujours les problèmes de guillemets:
Code:

1 2 PRINT 'BACKUP DATABASE [' + @name + '] TO DISK = [' + @filename + '] WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, STATS = 5'
Pareil j'ai essayé dans tous les sens sauf celui qu'il faut j'imagine...

édit: ça a l'air bon avec
Code:

1 2 PRINT 'BACKUP DATABASE [' + @name + '] TO DISK = ''[' + @filename + ']'' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, STATS = 5'
Ce qui ne va pas maintenant c'est le chemin :aie: Il pointe vers C:\program files...

édit 2 : Ça marche si je copie-colle le contenu du "PRINT" avec 3 ' ... et en virant les crochets...
Si j'enlève le "PRINT" et utilise le script tel quel, ça me rend une erreur sur le nom de la base après BACKUP DATABASE sûrement à cause des crochets... Et si je les enlève ça ne passe pas non plus...
13/09/2016, 14h33
nutopia

Bon c'est réglé pour le problème au-dessus. J'ai juste utilisé @name sans ' ni rien ...

Maintenant j'ai un souci de nom de bases trop longs! En effet, les noms de bases sont tronqués par le script.
( Voici la raison du nom trop long: http://www.developpez.net/forums/d16...epoint-2010-a/ )
Si quelqu'un sait comment outre passer ça? Merci!

Edit:
Autre problème: le script ne crée pas les fichiers de sauvegarde malgré le message comme quoi la sauvegarde s'est terminée correctement. Ce que je ne comprends toujours pas c'est qu'avec un copier-coller du résultat du "PRINT" tout marche...

Voici le script que j'utilise maintenant:

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
DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name
 
 
-- specify database backup directory
SET @path = '\\serveurdistant\backupdir\'  
 
 
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
 
 
DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('model','tempdb')  -- exclude these databases
 
 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   
 
 
WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
        print 'BACKUP DATABASE [' + @name + '] TO DISK = ''' + @filename + '''
		WITH NOFORMAT, INIT, NAME = ''' + @name + ''', SKIP, NOREWIND, NOUNLOAD,  STATS = 5'
 
       FETCH NEXT FROM db_cursor INTO @name   
END   
 
 
CLOSE db_cursor   
DEALLOCATE db_cursor

Ici, tout se passe bien.
Dès que j'enlève le "PRINT", j'obtiens:

Code:

1
2
3
4
5
6
7
8
9
10
Msg*911, Niveau*16, État*11, Ligne*28
La base de données '' + @name + '' n'existe pas. Assurez-vous que le nom a été saisi correctement.
Msg*3013, Niveau*16, État*1, Ligne*28
BACKUP DATABASE s'est terminé anormalement.
Msg*911, Niveau*16, État*11, Ligne*28
La base de données '' + @name + '' n'existe pas. Assurez-vous que le nom a été saisi correctement.
Msg*3013, Niveau*16, État*1, Ligne*28
BACKUP DATABASE s'est terminé anormalement.
Msg*911, Niveau*16, État*11, Ligne*28 
Etc.

Merci pour votre aide!

Pour l'autre problème (les noms de base trop long) j'imagine que je peux me servir de l'identifiant de la base? Mais je ne sais pas comment l'intégrer au script.

15/09/2016, 10h24
Bernardos

Bonjour,
que donne le print ?
Cordialement,

Bonjour,

voici le résultat du print:

Code:

1
2
3
4
5
BACKUP DATABASE [master] TO DISK = '\\serveurdistant\backupdir\master_20160916.BAK'
      	WITH NOFORMAT, INIT, NAME = 'master', SKIP, NOREWIND, NOUNLOAD,  STATS = 5
BACKUP DATABASE [msdb] TO DISK = '\\serveurdistant\backupdir\msdb_20160916.BAK'
      	WITH NOFORMAT, INIT, NAME = 'msdb', SKIP, NOREWIND, NOUNLOAD,  STATS = 5
[ etc. avec une ligne par base de données]

Merci!

16/09/2016, 12h10
Bernardos
heuh j'ai peur d'avoir compris ;)
ton print, tu l'enlèves certe... mais tu le remplaces par quoi?
dans ta déclaration tu ajoutes

Code:

Declare @SQLCMD nvarchar(250)

et tu remplaces ton print par
Code:

1 2 3 Set @SQLCMD = 'BACKUP DATABASE [' + @name + '] TO DISK = ''' + @filename + ''' WITH NOFORMAT, INIT, NAME = ''' + @name + ''', SKIP, NOREWIND, NOUNLOAD, STATS = 5' exec sp_executesql @SQLCMD
Cordialement,

Merci pour ton aide Bernardos!
Le script s'exécute avec quelques erreurs qui ont l'air de venir des noms des bases:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Msg*105, Niveau*15, État*1, Ligne*2
Ouvrez les guillemets après la chaîne de caractères 'Application_Registry_Ser'.
Msg*102, Niveau*15, État*1, Ligne*2
Syntaxe incorrecte vers 'Application_Registry_Ser'.
Msg*105, Niveau*15, État*1, Ligne*2
Ouvrez les guillemets après la chaîne de caractères 'Application de service d'.
Msg*102, Niveau*15, État*1, Ligne*2
Syntaxe incorrecte vers 'Application de service d'.
Msg*105, Niveau*15, État*1, Ligne*2
Ouvrez les guillemets après la chaîne de caractères 'Application de service d'.
Msg*102, Niveau*15, État*1, Ligne*2
Syntaxe incorrecte vers 'Application de service d'.
Msg*105, Niveau*15, État*1, Ligne*2
Ouvrez les guillemets après la chaîne de caractères 'Application de service d'.
Msg*102, Niveau*15, État*1, Ligne*2
Syntaxe incorrecte vers 'Application de service d'.
Msg*105, Niveau*15, État*1, Ligne*2
Ouvrez les guillemets après la chaîne de caractères 'Bdc_Service_DB_e5097008a27448c'.
Msg*102, Niveau*15, État*1, Ligne*2
Syntaxe incorrecte vers 'Bdc_Service_DB_e5097008a27448c'.

Il faut savoir qu'il y a des bases dont les noms contiennent des espaces et des accents...

Pour en revenir à ta correction, comment se fait-il que mon script ne fonctionnait pas avec seulement la commande SQL BACKUP et qu'il faille l'exécuter avec la PS?

Merci!

16/09/2016, 15h21
SQLpro

Citation:

Envoyé par nutopia

Il faut savoir qu'il y a des bases dont les noms contiennent des espaces et des accents...

C'est pour cela que dans mes article je donne une fonction pour nettoyer de telle conneries avant de créer le script de sauvegarde :
http://blog.developpez.com/sqlpro/p8...ec_nettoyage_d
http://blog.developpez.com/sqlpro/p6...ses_de_donnees

A +
16/09/2016, 16h02
nutopia

Je ne suis pas autorisé à modifier ces noms à la con...
18/09/2016, 13h33
SQLpro

Citation:

Envoyé par nutopia

Je ne suis pas autorisé à modifier ces noms à la con...

Au lieu de dire n'importe quoi, lisez les articles, vous ferez preuve d'intelligence !!!

En effet, cette fonction ne propose pas de modifier le noms des bases de données, ce qui serait stupide, mais de remplacer les caractères parasites dans les noms illicites afin de pouvoir être utilisé d'autre part, par exemple et justement si vous l'aviez lu, dans un nom de fichier !

A +
19/09/2016, 09h49
nutopia

Calmez-vous SVP. Ça doit être mon "à la con" qui vous a excité. Il allait pourtant dans votre sens et faisait écho à votre "de telle_ conneries". Etant bien d'accord que mettre des accents et des espaces dans un nom de base de données est une belle connerie...

Je ne vois pas où je dis "n'importe quoi" dans l'autre partie de la phrase: "Je ne suis pas autorisé à modifier ces noms". C'est un fait: je ne suis pas autorisé à modifier ces noms. Mais effectivement je n'ai pas lu votre lien étant donné que vous l'aviez présenté ainsi:"un nettoyage". Avouez que c'est confusant.

Là je lis vos scripts et n'y comprends pas grand-chose mais je vais essayer de les adapter à mon cas...
Bref, je vous remercie pour vos scripts !

A l'exécution de P_SAVE_DB, sur une base dont le nom comporte des espaces, j'obtiens:

Code:

1
2
3
4
5
6
7
8
9
10
 
Msg*8164, Niveau*16, État*1, Procédure*P_FILES, Ligne*27
Une instruction INSERT EXEC ne peut pas être imbriquée.
 
(0*ligne(s) affectée(s))
1280 pages traitées pour la base de données 'Application de service de profil utilisXXX', fichier 'Application de service de profil utilisXXX' dans le fichier 1.
4 pages traitées pour la base de données 'Application de service de profil utilisXXX', fichier 'Application de service de profil utilisXXX_log' dans le fichier 1.
BACKUP DATABASE a traité avec succès 1284 pages en 0.150*secondes (66.829*Mo/s).
 
(0*ligne(s) affectée(s))

De même avec une base dont le nom comporte des accents:

Code:

1
2
3
4
5
6
7
8
9
Msg*8164, Niveau*16, État*1, Procédure*P_FILES, Ligne*27
Une instruction INSERT EXEC ne peut pas être imbriquée.
 
(0*ligne(s) affectée(s))
296 pages traitées pour la base de données 'Service de métadonnées géréesXXX', fichier 'Service de métadonnées géréesXXX' dans le fichier 1.
2 pages traitées pour la base de données 'Service de métadonnées géréesXXX', fichier 'Service de métadonnées géréesXXX_log' dans le fichier 1.
BACKUP DATABASE a traité avec succès 298 pages en 0.074*secondes (31.447*Mo/s).
 
(0*ligne(s) affectée(s))

Les .bak sont bien créés avec des noms débarrassés des espaces et accents.

Je ne sais pas à quel point ce message d'erreur est impactant.

19/09/2016, 18h50
SQLpro
Citation:
Envoyé par nutopia

A l'exécution de P_SAVE_DB, sur une base dont le nom comporte des espaces, j'obtiens:

Code:

1 2 3 4 Msg*8164, Niveau*16, État*1, Procédure*P_FILES, Ligne*27 Une instruction INSERT EXEC ne peut pas être imbriquée. ...
En général ce message se produit si vous essayez d'encapsuler ce code dans une transaction... Avez vous initialiser cette sauvegarde avec une transaction ?

A +
20/09/2016, 09h57
aieeeuuuuu
Bonjour,

Ce message se produit lorsque vous effectuez un INSERT INTO UneTable EXEC UneProcedure, alors que UneProcedure effectue elle-même un INSERT INTO UneTable EXEC UneAutreProcedure : Vous ne pouvez pas imbriquer les INSERT INTO EXEC.

Ici, la procédure P_FILES effectue ceci :
Code:

1 2 3 INSERT INTO @T EXEC xp_cmdshell @COM;
Vous ne pouvez donc pas faire de
Code:

1 2 INSERT INTO @TF (FNAME, FDATE, FSIZE) EXEC P_FILES
20/09/2016, 14h54
nutopia

Merci pour vos réponses.

@sqlpro:
Non, je n'ai pas utilisé de "BEGIN TRAN", j'ai directement tapé:

Code:

EXEC S_MAINT.P_SAVE_DB 'Service de métadonnées gérées_d333429a41324263972dae7738100f73', 'S:\SauvSQL\', 1, 3;

@aieeeuuuuu:
J'ai effectivement utilisé les scripts tels-quels. Je ne les comprends pas assez pour pouvoir les modifier et corriger cette imbrication.

Pour en revenir peut-être à quelque chose de plus simple: sans procédures, fonctions, imbrications incompatibles etc. Si je peux juste utiliser un "id" de base de données au lieu des noms des bases dans mon script de base, je devrais pouvoir sauvegarder toutes les bases sans erreurs...