Accueil > > > SQL SERVER - PROCÉDURE STOCKÉE FAISANT UN BACKUP DE TOUTES LES BASES ET JOURNAUX DE TRANSACTION AUTOMATIQUEMENT
SQL SERVER - PROCÉDURE STOCKÉE FAISANT UN BACKUP DE TOUTES LES BASES ET JOURNAUX DE TRANSACTION AUTOMATIQUEMENT
Information sur la source
Description
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
- ----------------------------------------------------------------------------------------------------------------
- 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\'
-
-
-
-
----------------------------------------------------------------------------------------------------------------
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___)
Sources de la même categorie
Commentaires et avis
Discussions en rapport avec ce code source dans le forum
Sauvegarde automatique sqlserver 2000 [ par jojos89 ]
Salut a tous,J'ai une base de données en local sur mon pc (avec sqlserver 2000)que je voudrais sauvegardé automatique (tous les soir) s
aide pour SQLSERVER [ par nagrom_om ]
salut,quelqu'un sait il comment peut on avoir un listing des requetes qui sont passées sous SQL Server ??Merci !
ms access - sqlserver - procédurestockée [ par metasky ]
bonjour,je suis actuellement sur une appli liant access et sqlserver...quand je consulte mes procédures stockées dans mon projet access les
sauvegarde access [ par fififoufou2 ]
Bonjour, Est-il possible de sauvegarder une bdd Access à l'aide d'une commande sql. A la limite, en exporter les données au format word, excel ou autr
Backup et restauration avec les recovery model [ par bubix ]
Bonsoir, Je veux réaliser un backup complet de ma base. Ensuite, je veux faire un backup de journal des transactions.J'utilise la commande
procedure stocké sqlhelper [ par htmlchef ]
Bonjour, Je developpe en VB.NET et j utilise la classe Sqlhelper de puis l'éspace de noms Microsoft.ApplicationBlocks.Data pour appeler une Proce
Quel outil pour SqlServer [ par padurand ]
Mon site tourne avec une base de donnees Access, et des pages ASP.Je voudrais remplacer Access par SqlServer ... a priori il ne devrait pas y avoir de
exporter des donnees de sqlserver 2005 vers access (dts) [ par tenrod ]
Salut a tous ,Voila je travail avec une base sql server 2005 et je voudrais exporter le résultat d'une requete (a partir de ma base sql server) d
procedures stockée avec Mysql [ par emna_bz ]
Bonjour ,J'ai crée la procedure stockée suivante: CREATE procedure spGetCategorie(Catlien varchar(30) ,out Name varchar(30) ) BEGI
1ere procedure stockée [ par matlocker ]
Bonjour a tous,Je realise acuellement ma premiere procedure stockée sous SQL Server 2000.Je dois recuperer dans une table la liste des symbol qui
|
Derniers Blogs
[MIX10] RELEASE CANDIDATE DE SILVERLIGHT 4 ET RIA SERVICES[MIX10] RELEASE CANDIDATE DE SILVERLIGHT 4 ET RIA SERVICES par Audrey
C'est enfin officiel, grâce au MIX 2010, les Release Candidate de Silverlight 4 et de RIA Services sont sorties ! Pour les télécharger, voici les liens : Silverlight 4 RC : http://silverlight.net/getstarted/silverlight-4/ RIA Services RC : http://www.micr...
Cliquez pour lire la suite de l'article par Audrey [WINDOWSPHONE7] LECTEUR DE FLUX RSS[WINDOWSPHONE7] LECTEUR DE FLUX RSS par Vko
Parce que j'aime pas tester à moitié, je me suis amusé à développer un petit lecteur de flux RSS avec un look qui vous rappellera surement quelque chose :) La RC de Visual Studio est plutôt molle mais fonctionne correctement. L'émulateur est pas...
Cliquez pour lire la suite de l'article par Vko [WP7] L'éMULATEUR WINDOWS PHONE 7 EST MULTITâCHE[WP7] L'éMULATEUR WINDOWS PHONE 7 EST MULTITâCHE par KooKiz
Une question qui reste en suspend concernant Windows Phone 7 est de savoir si le système permettra à plusieurs applications de s'exécuter simultanément, ou si un système de mise en pause similaire à celui de l'iPhone sera adopté. Même si cela ne perm...
Cliquez pour lire la suite de l'article par KooKiz PRéSENTATION CLUB MOSS CE JOUR "LA NOUVELLE ARCHITECTURE DE SERVICES SHAREPOINT 2010".PRéSENTATION CLUB MOSS CE JOUR "LA NOUVELLE ARCHITECTURE DE SERVICES SHAREPOINT 2010". par Patrick Guimonet
Ca y est la sortie RTM de SharePoint 2010 est annoncée pour le 16 avril 2010 (annonce faite ce jour par Erol Giraudy lors de la réunion du club MOSS). Vous pourrez trouver ici les slides de ma présentation du jour dans le cadre du club MOSS : 201...
Cliquez pour lire la suite de l'article par Patrick Guimonet [WINDOWPHONE7] PREMIERS PAS[WINDOWPHONE7] PREMIERS PAS par Vko
Hop voici les premiers pas avec les outils de développement pour Windows Phone 7. Premier changement suite à l'installation des outils : les type de projets pour la plateforme Windows Phone 7. Les projets se découpent en deux groupes : Les projet...
Cliquez pour lire la suite de l'article par Vko
Forum
MCDMCD par debinfo
Cliquez pour lire la suite par debinfo
Logiciels
Xilisoft Convertisseur Vidéo Ultimate (5.1.39.0305)XILISOFT CONVERTISSEUR VIDéO ULTIMATE (5.1.39.0305)Xilisoft Convertisseur Vidéo Ultimate est un outil puissant de conversion vidéo, facile à utilise... Cliquez pour télécharger Xilisoft Convertisseur Vidéo Ultimate Xilisoft DVD Ripper Ultimate (5.0.64.0304)XILISOFT DVD RIPPER ULTIMATE (5.0.64.0304)Xilisoft DVD Ripper Ultimate est un logiciel excellent pour copier et convertir DVD vers presque ... Cliquez pour télécharger Xilisoft DVD Ripper Ultimate Rigs of Rods (63.3)RIGS OF RODS (63.3)c'est un jeu de multi-simulation camions,autobus voitures, avions, bateaux, hélicoptère avec défo... Cliquez pour télécharger Rigs of Rods Konvertor (4.00)KONVERTOR (4.00)Le logiciel est un gestionnaire multimedia affichant, jouant et convertissant plus de 2000 format... Cliquez pour télécharger Konvertor
|