Laboratoire Microsoft |  CertifExpress 
 Le Forum de Réfèrence sur les Technologies Microsoft - http://www.forum-microsoft.org

[SQL Express 2005] Sauvegarde BdB automatique [réglé]

Un forum dédié au serveur de Base de Données Made in Microsoft

Modérateurs : Thierry DEMAN (MVP Exchan, ygainche, Modérateurs, Modérateurs_Applicatifs

[SQL Express 2005] Sauvegarde BdB automatique [réglé]

Message par gth75000 » Mar 22 Avr 2008, 10:06

Bonjour,

J'ai actuellement un Serveur SQL Express 2005 avec plusieurs instances que je souhaiterais sauvegarder. J'ai regardé sur le forum et je suis tombé sur ce post : http://www.forum-microsoft.org/topic78370.html mais cette solution me plait qu'à moitié. En effet, avec mon ami google, j'ai pu trouver une méthode de sauvegarde automatique à l'adresse suivante : http://fromelard.free.fr/Scripts/SQL_BackupExpress/SQL_Backup-SQL-Express.pdf

Cette procédure me paraît plus adéquate et plus professionnelle que le xcopy. Malheureusement, elle parle d'une procédure stockée dans le master de SQL Express et lorsque je lance le script (adapté à mon installation), il me dit que la procédure stockée n'a pas été trouvée :
D:\Scripts>sqlcmd -S SERVEUR\Instance -U sa -P mdp_sa -Q "EXEC master.dbo.SP_System_SauvegardesTotales 1, 1, 'D:\Backup sql\instance'"
Message 2812, niveau 16, état 62, serveur SERVEUR\Instance, ligne 1 Procédure stockée 'master.dbo.SP_System_SauvegardesTotales' introuvable.

Ce qui m'intrigue c'est le nom de la procédure "master.dbo.SP_System_SauvegardesTotales" surtout "SauvegardesTotales".

Avez-vous déjà utilisé ce script ou une autre procédure pour la sauvegarde de vos base SQL Express ?

Merci de vos retours,

Cordialement,

GTH75000
Dernière édition par gth75000 le Ven 25 Avr 2008, 10:44, édité 3 fois.
Avatar de l’utilisateur
gth75000
Master
Master
 
Message(s) : 665
Inscription : Mar 21 Juin 2005, 11:53
Localisation : BZH/Rennes

Message par berimi » Mar 22 Avr 2008, 20:11

Il est claire que la procédure stockée en question n'est pas une procédure système puisqu'elle porte un nom en français.

Le code de la procédure est le suivant:
Code : Tout sélectionner
----------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.SP_System_SauvegardesTotales
/*
   Permet de sauvegarder toutes les bases et logs et de les compresser (CAB)
    de toutes les bases du serveur SQL Server
*/   @SauvegardeLogs      AS int,
   @SauvegardeBases   AS int,
   @RepertoireSauvegarde   AS VarChar(8000)
AS
-----------------------------------------------------------------
-- Déclaration des Variables
DECLARE @name sysname
DECLARE @status int
DECLARE @TestAutoTruncate bit
DECLARE @LaRequette varchar(8000)
DECLARE @DateJour varchar(20)
DECLARE @Repertoire varchar(8000)
DECLARE @RepertoireDefaut varchar(100)
-----------------------------------------------------------------
-- Initialisation des Variables déclarées
SET @DateJour = REPLACE(CONVERT(VARCHAR, GetDate(), 102), '.', '_')
SET @RepertoireDefaut = 'C:\BACKUPBASES\'
-----------------------------------------------------------------
PRINT '-----------------------------------------------------'
PRINT '    DATE DE LA SAUVEGARDE LANCEE : '+ @DateJour
PRINT '-----------------------------------------------------'
-----------------------------------------------------------------
-- Vérification du paramètre du répertoire des Bases et Logs
IF RTRIM(@RepertoireSauvegarde) != ''
   BEGIN
      PRINT 'Répertoire Fourni à la SP'
      IF RIGHT(@RepertoireSauvegarde, 1) != '\'
         SET @Repertoire = @RepertoireSauvegarde + '\'
      ELSE
         SET @Repertoire = @RepertoireSauvegarde
   END
ELSE
   BEGIN
      PRINT 'Répertoire Non Fourni à la SP - Création du répertoire par défaut : '+ @RepertoireDefaut
      SET @LaRequette = 'master.dbo.xp_cmdshell ''MKDIR '+ @RepertoireDefaut +''', NO_OUTPUT '
      PRINT 'Requette : '+ @LaRequette
      EXECUTE (@LaRequette)
      SET @Repertoire = @RepertoireDefaut
   END
PRINT 'Répertoire de Sauvegarde des Bases et Logs : '+ @Repertoire
   
-----------------------------------------------------------------
-- Déclaration du Curseur sur la liste des Bases de données
DECLARE TESTCURSEUR CURSOR
   FOR SELECT master.dbo.sysdatabases.name, master.dbo.sysdatabases.status
   FROM master.dbo.sysdatabases
--      WHERE (master.dbo.sysdatabases.status & 8)!= 8
--      WHERE master.dbo.sysdatabases.name  NOT IN ('tempdb', 'model', 'pubs')
-- Ouverture du Curseur
OPEN TESTCURSEUR
FETCH NEXT FROM TESTCURSEUR
INTO @name, @status
-- Bouclage sur le curseur défini
WHILE @@FETCH_STATUS = 0
BEGIN
   -- Vérifie que la base de transaction n'est pas en mode simple (donc pas de log à sauver)
   SET @TestAutoTruncate = @status & 8
/*
   -- Suivi des valeurs pour tests
   PRINT 'BASE : '+ @name
   PRINT 'TEST TRUNCATE : '+ CAST(@TestAutoTruncate AS VARCHAR)
   PRINT 'Valeur Status : '+ CAST(@status AS VARCHAR)
*/   -----------------------------------------------------------------
   -- Vérification de la sauvegarde des Logs de Bases demandée
   IF (@SauvegardeLogs = 1) AND (@TestAutoTruncate = 0)
   BEGIN
      PRINT ''
      PRINT '-----------------------------------------------------'
      PRINT 'BACKUP DU LOG DE LA BASE DE DONNEES : '+ @name
   
      ----------------- LOGS -----------------------------
      -- Sauvegarde du Log de transaction courant dans le Curseur
      SET @LaRequette = 'USE '+ @name +' EXEC sp_addumpdevice ''DISK'', '''+ @name +'Log'', '''+ @Repertoire + @name +'Log.BAK'' BACKUP LOG '+ @name +' TO '+ @name +'Log'
      PRINT 'Requette : '+ @LaRequette
      EXECUTE (@LaRequette)
      -- Suppression du device déclaré dans la table Master.Sysdevices
      SET @LaRequette = 'USE '+ @name +' EXEC sp_dropdevice '''+ @name +'Log'' '
      PRINT 'Requette : '+ @LaRequette
      EXECUTE (@LaRequette)
   
      -- Archivage du fichier BAK avec le format Windows CAB
      SET @LaRequette = 'master.dbo.xp_cmdshell ''C:\WINDOWS\system32\makecab /V[1] /L '+ @Repertoire +' '+ @Repertoire + @name +'Log.BAK '+ @name +'Log_'+ @DateJour +'.cab'', NO_OUTPUT '
      PRINT 'Requette : '+ @LaRequette
      EXECUTE (@LaRequette)
   
      -- Suppression du fichier BAK
      SET @LaRequette = 'master.dbo.xp_cmdshell ''DEL '+ @Repertoire + @name +'Log.BAK'', NO_OUTPUT '
      PRINT 'Requette : '+ @LaRequette
      EXECUTE (@LaRequette)
   END
   -----------------------------------------------------------------
   -- Vérification de la sauvegarde des Bases de données demandée
   IF @SauvegardeBases = 1
   BEGIN
      PRINT ''
      PRINT '-----------------------------------------------------'
      PRINT 'BACKUP DE LA BASE DE DONNEES : '+ @name
      ----------------- BASES ----------------------------
      -- Sauvegarde de la base courante dans le Curseur
      SET @LaRequette = 'USE '+ @name +' BACKUP DATABASE '+ @name +' TO DISK ='''+ @Repertoire + @name +'.BAK'''
      PRINT 'Requette : '+ @LaRequette
      EXECUTE (@LaRequette)
   
      -- Archivage du fichier BAK avec le format Windows CAB
      SET @LaRequette = 'master.dbo.xp_cmdshell ''C:\WINDOWS\system32\makecab /V[1] /L '+ @Repertoire +' '+ @Repertoire + @name +'.BAK '+ @name +'_'+ @DateJour +'.cab'', NO_OUTPUT '
      PRINT 'Requette : '+ @LaRequette
      EXECUTE (@LaRequette)
   
      -- Suppression du fichier BAK
      SET @LaRequette = 'master.dbo.xp_cmdshell ''DEL '+ @Repertoire + @name +'.BAK'', NO_OUTPUT '
      PRINT 'Requette : '+ @LaRequette
      EXECUTE (@LaRequette)
   END
   PRINT ''
   PRINT '-----------------------------------------------------'
   -----------------------------------------------------------------
   -- Avance le curseur d'un Cran
   FETCH NEXT FROM TESTCURSEUR
   INTO @name, @status
END
-----------------------------------------------------------------
-- Fermeture du Curseur
CLOSE TESTCURSEUR
-- Libération de la mémoire prise par le Curseur
DEALLOCATE TESTCURSEUR
-----------------------------------------------------------------
GO
----------------------------------------------------------------------------------------------------------------
-- Vous pouvez utiliser cette SP comme suit :
-- EXEC dbo.SP_System_SauvegardesTotales 1, 0, 'C:\MonBacUpDeBases\' > il archivera les Logs de Transaction sans les bases
-- EXEC dbo.SP_System_SauvegardesTotales 0, 1, 'C:\MonBacUpDeBases\' > il archivera les bases sans les Logs de Transaction
-- EXEC dbo.SP_System_SauvegardesTotales 1, 1, '' > il archivera les bases et les Logs de Transaction dans le répertoire par défaut 'C:\BACKUPBASES\'


Je l'ai trouvée sur internet.

Hope it helps,

T.Berimi
berimi
Member
Member
 
Message(s) : 97
Inscription : Jeu 12 Mai 2005, 14:42

Message par gth75000 » Mer 23 Avr 2008, 14:59

Bonjour,

Moi je dis : "Chapeau Bas Monsieur !". Ca marche à merveille. Merci beaucoup.

Il reste juste l'utilitaire de compression qui ne fonctionne pas. En effet, il n'a pas l'air de trop aimer le "xp_cmdshell". Pourtant, il ne me renvoi pas d'erreur. J'ai redémarrer le service SQL et toujours le même problème.

Une idée peut-être ?

Encore merci,

GTH75000
Avatar de l’utilisateur
gth75000
Master
Master
 
Message(s) : 665
Inscription : Mar 21 Juin 2005, 11:53
Localisation : BZH/Rennes

Message par berimi » Mer 23 Avr 2008, 20:21

Avez-vous activé le xp_cmdshell?

T.Berimi
berimi
Member
Member
 
Message(s) : 97
Inscription : Jeu 12 Mai 2005, 14:42

Re: [SQL Express 2005] Sauvegarde BdB automatique

Message par gth75000 » Jeu 24 Avr 2008, 18:56

Bonjour,

J'ai activé le xp-cmdshell dans "Configuration de la surface d'exposition SQL Server". J'ai effectué quelques tests hier soir et aujourd'hui, en fait, il faut rajouter des guillemets à la commande envoyer à xp_cmdshell.

Il fallait rajouter des guillemets à la procédure :

Code : Tout sélectionner
SET @LaRequette = 'master.dbo.xp_cmdshell '+'''C:\WINDOWS\system32\makecab /V[1] "'+ @Repertoire + @name +'Log.BAK" "'+ @Repertoire + @name +'Log_'+ @DateJour +'.cab"'''+', NO_OUTPUT '
...
SET @LaRequette = 'master.dbo.xp_cmdshell ''DEL "'+ @Repertoire + @name +'Log.BAK"'', NO_OUTPUT '
...
SET @LaRequette = 'master.dbo.xp_cmdshell '+'''C:\WINDOWS\system32\makecab /V[1] "'+ @Repertoire + @name +'.BAK" "'+ @Repertoire + @name +'_'+ @DateJour +'.cab"'''+', NO_OUTPUT '
...
SET @LaRequette = 'master.dbo.xp_cmdshell ''DEL "'+ @Repertoire + @name +'.BAK"'', NO_OUTPUT '


Cela permet de gérer les espaces dans le nom du répertoire.

Merci encore,

Cordialement,

GTH75000
Avatar de l’utilisateur
gth75000
Master
Master
 
Message(s) : 665
Inscription : Mar 21 Juin 2005, 11:53
Localisation : BZH/Rennes

Re: [SQL Express 2005] Sauvegarde BdB automatique

Message par gth75000 » Jeu 24 Avr 2008, 19:03

Re,

Juste un petit correctif avant de partir. Cela marche bien pour les bases systèmes et une base de données mais dès que j'en ai plusieurs, il ne sauvegarde pas. Je regarde cela et je vous tiens au courant.

Bonne soirée,

GTH75000
Avatar de l’utilisateur
gth75000
Master
Master
 
Message(s) : 665
Inscription : Mar 21 Juin 2005, 11:53
Localisation : BZH/Rennes

Re: [SQL Express 2005] Sauvegarde BdB automatique

Message par gth75000 » Ven 25 Avr 2008, 10:22

Bonjour,

Après analyse, il s'avère que c'est le tiret dans le nom de mes bases (exemple : nom-bdb1). Quand je modifie le script pour inclure des quotes autour du nom ('nom-bdb1') il m'indique qu'il y a un erreur de syntaxe.
Ensuite quand je mets des guillemets ("nom-bdb1"), il m'indique :
Requette : USE "nom-bdb1" EXEC sp_addumpdevice 'DISK', 'nom-bdb1Log', 'D:\Backup sql\bdb\nom-bdb1Log.BAK' BACKUP LOG nom-bdb1 TO nom-bdb1Log
Message 102, niveau 15, état 1, serveur SERVEUR\SQLEXPRESS, ligne 1
Syntaxe incorrecte vers '-'.


Voici le script que j'utilise afin que la commande BACKUP gère les espaces et les tirets dans les noms :
Code : Tout sélectionner
----------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.SP_System_SauvegardesTotales
/*
   Permet de sauvegarder toutes les bases et logs et de les compresser (CAB)
   de toutes les bases du serveur SQL Server
*/   @SauvegardeLogs      AS int,
   @SauvegardeBases   AS int,
   @RepertoireSauvegarde   AS VarChar(8000)
AS
-----------------------------------------------------------------
-- Déclaration des Variables
DECLARE @name sysname
DECLARE @status int
DECLARE @TestAutoTruncate bit
DECLARE @LaRequette varchar(8000)
DECLARE @DateJour varchar(20)
DECLARE @Repertoire varchar(8000)
DECLARE @RepertoireDefaut varchar(100)
-----------------------------------------------------------------
-- Initialisation des Variables déclarées
SET @DateJour = REPLACE(CONVERT(VARCHAR, GetDate(), 102), '.', '_')
SET @RepertoireDefaut = 'C:\BACKUPBASES\'
-----------------------------------------------------------------
PRINT '-----------------------------------------------------'
PRINT '    DATE DE LA SAUVEGARDE LANCEE : '+ @DateJour
PRINT '-----------------------------------------------------'
-----------------------------------------------------------------
-- Vérification du paramètre du répertoire des Bases et Logs
IF RTRIM(@RepertoireSauvegarde) != ''
   BEGIN
      PRINT 'Répertoire Fourni à la SP'
      IF RIGHT(@RepertoireSauvegarde, 1) != '\'
         SET @Repertoire = @RepertoireSauvegarde + '\'
      ELSE
         SET @Repertoire = @RepertoireSauvegarde
   END
ELSE
   BEGIN
      PRINT 'Répertoire Non Fourni à la SP - Création du répertoire par défaut : '+ @RepertoireDefaut
      SET @LaRequette = 'master.dbo.xp_cmdshell ''MKDIR '+ @RepertoireDefaut +''', NO_OUTPUT '
      PRINT 'Requette : '+ @LaRequette
      EXECUTE (@LaRequette)
      SET @Repertoire = @RepertoireDefaut
   END
PRINT 'Répertoire de Sauvegarde des Bases et Logs : '+ @Repertoire
   
-----------------------------------------------------------------
-- Déclaration du Curseur sur la liste des Bases de données
DECLARE TESTCURSEUR CURSOR
   FOR SELECT master.dbo.sysdatabases.name, master.dbo.sysdatabases.status
   FROM master.dbo.sysdatabases
--      WHERE (master.dbo.sysdatabases.status & 8)!= 8
--      WHERE master.dbo.sysdatabases.name  NOT IN ('tempdb', 'model', 'pubs')
-- Ouverture du Curseur
OPEN TESTCURSEUR
FETCH NEXT FROM TESTCURSEUR
INTO @name, @status
-- Bouclage sur le curseur défini
WHILE @@FETCH_STATUS = 0
BEGIN
   -- Vérifie que la base de transaction n'est pas en mode simple (donc pas de log à sauver)
   SET @TestAutoTruncate = @status & 8
/*
   -- Suivi des valeurs pour tests
   PRINT 'BASE : '+ @name
   PRINT 'TEST TRUNCATE : '+ CAST(@TestAutoTruncate AS VARCHAR)
   PRINT 'Valeur Status : '+ CAST(@status AS VARCHAR)
*/   -----------------------------------------------------------------
   -- Vérification de la sauvegarde des Logs de Bases demandée
   IF (@SauvegardeLogs = 1) AND (@TestAutoTruncate = 0)
   BEGIN
      PRINT ''
      PRINT '-----------------------------------------------------'
      PRINT 'BACKUP DU LOG DE LA BASE DE DONNEES : '+ @name
   
      ----------------- LOGS -----------------------------
      -- Sauvegarde du Log de transaction courant dans le Curseur
      SET @LaRequette = 'USE "'+ @name +'" EXEC sp_addumpdevice ''DISK'', "'+ @name +'Log", "'+ @Repertoire + @name +'Log.BAK" BACKUP LOG "'+ @name +'" TO "'+ @name +'Log"'
      PRINT 'Requette : '+ @LaRequette
      EXECUTE (@LaRequette)
      -- Suppression du device déclaré dans la table Master.Sysdevices
      SET @LaRequette = 'USE "'+ @name +'" EXEC sp_dropdevice "'+ @name +'Log" '
      PRINT 'Requette : '+ @LaRequette
      EXECUTE (@LaRequette)
   
      -- Archivage du fichier BAK avec le format Windows CAB
      SET @LaRequette = 'master.dbo.xp_cmdshell '+'''C:\WINDOWS\system32\makecab /V[1] "'+ @Repertoire + @name +'Log.BAK" "'+ @Repertoire + @name +'Log_'+ @DateJour +'.cab"'''+', NO_OUTPUT '
      PRINT 'Requette : '+ @LaRequette
      EXECUTE (@LaRequette)
   
      -- Suppression du fichier BAK
      SET @LaRequette = 'master.dbo.xp_cmdshell ''DEL "'+ @Repertoire + @name +'Log.BAK"'', NO_OUTPUT '
      PRINT 'Requette : '+ @LaRequette
      EXECUTE (@LaRequette)
   END
   -----------------------------------------------------------------
   -- Vérification de la sauvegarde des Bases de données demandée
   IF @SauvegardeBases = 1
   BEGIN
      PRINT ''
      PRINT '-----------------------------------------------------'
      PRINT 'BACKUP DE LA BASE DE DONNEES : '+ @name
      ----------------- BASES ----------------------------
      -- Sauvegarde de la base courante dans le Curseur
      SET @LaRequette = 'USE "'+ @name +'" BACKUP DATABASE "'+ @name +'" TO DISK ='''+ @Repertoire + @name +'.BAK'''
      PRINT 'Requette : '+ @LaRequette
      EXECUTE (@LaRequette)
   
      -- Archivage du fichier BAK avec le format Windows CAB
      SET @LaRequette = 'master.dbo.xp_cmdshell '+'''C:\WINDOWS\system32\makecab /V[1] "'+ @Repertoire + @name +'.BAK" "'+ @Repertoire + @name +'_'+ @DateJour +'.cab"'''+', NO_OUTPUT '
      PRINT 'Requette : '+ @LaRequette
      EXECUTE (@LaRequette)
   
      -- Suppression du fichier BAK
      SET @LaRequette = 'master.dbo.xp_cmdshell ''DEL "'+ @Repertoire + @name +'.BAK"'', NO_OUTPUT '
      PRINT 'Requette : '+ @LaRequette
      EXECUTE (@LaRequette)
   END
   PRINT ''
   PRINT '-----------------------------------------------------'
   -----------------------------------------------------------------
   -- Avance le curseur d'un Cran
   FETCH NEXT FROM TESTCURSEUR
   INTO @name, @status
END
-----------------------------------------------------------------
-- Fermeture du Curseur
CLOSE TESTCURSEUR
-- Libération de la mémoire prise par le Curseur
DEALLOCATE TESTCURSEUR
-----------------------------------------------------------------
GO
----------------------------------------------------------------------------------------------------------------
-- Vous pouvez utiliser cette SP comme suit :
-- EXEC dbo.SP_System_SauvegardesTotales 1, 0, 'C:\MonBacUpDeBases\' > il archivera les Logs de Transaction sans les bases
-- EXEC dbo.SP_System_SauvegardesTotales 0, 1, 'C:\MonBacUpDeBases\' > il archivera les bases sans les Logs de Transaction
-- EXEC dbo.SP_System_SauvegardesTotales 1, 1, '' > il archivera les bases et les Logs de Transaction dans le répertoire par défaut 'C:\BACKUPBASES\'


Pour moi, il fonctionne bien. Si vous rencontrez quelques difficultés avec ce script, je suis preneur des modifications que vous apporterez afin qu'il soit le plus optimal possible.

Merci encore pour votre aide.

Cordialement,

GTH75000
Avatar de l’utilisateur
gth75000
Master
Master
 
Message(s) : 665
Inscription : Mar 21 Juin 2005, 11:53
Localisation : BZH/Rennes

Re: [SQL Express 2005] Sauvegarde BdB automatique [réglé]

Message par gth75000 » Ven 25 Avr 2008, 11:03

Re,

Le Backup vient de se terminer sans problème. Petite information pour économiser de l'espace disque :
Ne jamais hésiter à compresser les sauvegardes de ses bases de données !!!


A titre d'exemple :
L'intégralité de mes bases pèse environ 10 Go soit environ 10 240 Mo :roll: ==> une fois compressé 410 Mo soit un gain de presque 2 500 %

Impressionnante n'est-ce pas !!! 8O

C'était juste l'info complémentaire...

Comme quoi, les bases de données c'est vraiment plein de vide...

@ +,

GTH75000
Avatar de l’utilisateur
gth75000
Master
Master
 
Message(s) : 665
Inscription : Mar 21 Juin 2005, 11:53
Localisation : BZH/Rennes

Re: [SQL Express 2005] Sauvegarde BdB automatique [réglé]

Message par martoni35220 » Lun 22 Fév 2016, 12:16

Bonjour,

Le script est intéressant.

Est-ce qu'il est possible d'intégrer un envoi de mail avec les logs ?

En vous remerciant,

Martoni
martoni35220
Novice
Novice
 
Message(s) : 14
Inscription : Jeu 07 Jan 2016, 10:45


Retour vers SQL Server

Qui est en ligne ?

Utilisateur(s) parcourant ce forum : Aucun utilisateur inscrit et 8 invité(s)



Accueil | News | Articles | Tips | Outils | Certification | Easters Eggs
Essentiels | Glossaire | Vidos | Whitepapers | Essentiels | Bote Scripts
Conditions d'utilisation & Copyright | Respect de la vie prive