Sql server - procédure stockée faisant un backup de toutes les bases et journaux de transaction automatiquement

Soyez le premier à donner votre avis sur cette source.

Snippet vu 39 856 fois - Téléchargée 31 fois

Contenu du snippet

Dans le cadre des taches administratives il faut bien sur faire des BackUp des bases et Journaux de transaction de SQL Server.
Pour cela le plan de maintenance est utilisable, mais souvent peut poser problème.
Voila donc une Procédure Stockée qui permet de faire ces Backup puis d'enchainer par une compression de ces fichiers via les fichier Windows (CAB).

Vous pouvez spécifier à la procédure stockée le fait de vouloir ou non archiver les Bases, les Log de transaction et de spécifier le répertoire dans lequel il sauvera les fichiers.

Vous pouvez ensuite planifier l'exécution de cette procédure toutes les semaines voire tous les mois, via les jobs de SQL Agent.

Source / Exemple :


----------------------------------------------------------------------------------------------------------------
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\'

Conclusion :


En espérant que cette SP vous rende service.

Bon coding

Romelard Fabrice (Alias F___)

A voir également

Ajouter un commentaire

Commentaires

kraps04
Messages postés
3
Date d'inscription
vendredi 18 juillet 2008
Statut
Membre
Dernière intervention
16 janvier 2012
-
Bonjour,
j'ai testé cette procédure qui fonctionne très bien , seulement moi j'ai décidé d'en faire 2 procédures , une pour la sauvegarde des bases de données et une pour les journaux des transactions afin de sauvegarder les journaux plus régulièrement que les bases .
J'ai donc essayer de rajouter l'heure aux nom des fichier de sauvegardes des logs ,mais en vain ,l'heure s'écrivant sous cette forme HH:MM:SS ,les : ne sont pas utilisable dans un nom de fichier .. si vous auriez une réponse pour me venir en aide s'il vous plait .. merci
cs_fabrice69
Messages postés
1766
Date d'inscription
jeudi 12 octobre 2000
Statut
Modérateur
Dernière intervention
11 décembre 2013
4 -
Bonjour,
Dans ce cas, il faut mettre le nom de la base entre crochets [xxxxxxx]
Cordialement
Fabrice Romelard [MVP]
scub82
Messages postés
2
Date d'inscription
samedi 2 juillet 2005
Statut
Membre
Dernière intervention
24 octobre 2014
-
Bonjour,
J'ai testé cette procédure qui marche très bien néanmoins j'ai un problème concernant une des bases. Le back up ne se fait pas car il semble que le nom de a base comporte un espace.
Il y a t il un moyen de régler ce problème?

merci d'avance.
cs_fabrice69
Messages postés
1766
Date d'inscription
jeudi 12 octobre 2000
Statut
Modérateur
Dernière intervention
11 décembre 2013
4 -
Bonjour,
Cela vient du fait que cette procédure utilise après la sauvegarde un module de compression (standard sous Windows - MakeCab).
Pour exécuter cette commande, on doit utiliser la procédure system de SQL Server "dbo.xp_cmdshell" qui permet d'exécuter des fichiers exécutable dans le contexte SQL Server.
Cette procédure stockée est désactivée de base sous SQL Server 2005 et 2008. Il vous faut chercher dans les options pour la réactiver (je ne me souviens plus exactement ou mais ca doit être dans l'outil danalyse de la surface de mémoire).

Si vous ne voulez pas être géné avec ca, il suffit de désactiver (commentaire devant la ligne -- souc SQL Server) les lignes 129 et 134

Cordialement
Romelard Fabrice [MVP]
usager2009
Messages postés
1
Date d'inscription
vendredi 23 octobre 2009
Statut
Membre
Dernière intervention
23 octobre 2009
-
Bonjour,

J'ai le même questionnement que MATTZ. Est-ce possible d'utiliser cette procédure stockée avec "Microsoft SQL Server Management Studio Express". Si oui, comment faire étape par étape. Moi je réussis seulement à faire un backup de mes BD à partir de l'interface de la console de management ou en générant les scripts, ensuite les exécuter dans des batchs avec la commande sqlcmd. En fait, je me retrouves avec plusieurs batchs et tâches planifiées pour mes backups de BD, ce qui est lourd à gérer. Merci

Vous n'êtes pas encore membre ?

inscrivez-vous, c'est gratuit et ça prend moins d'une minute !

Les membres obtiennent plus de réponses que les utilisateurs anonymes.

Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.

Le fait d'être membre vous permet d'avoir des options supplémentaires.