SQL SERVER - PROCÉDURE STOCKÉE FAISANT UN BACKUP DE TOUTES LES BASES ET JOURNAUX DE TRANSACTION AUTOMATIQUEMENT

---

Commentaire sur cette source (11)
Ajouter un commentaire et/ou une note

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.

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

En espérant que cette SP vous rende service.

Bon coding

Romelard Fabrice (Alias F___)

• SQL SERVER - OBTENIR LES VALEURS CORRESPONDANT A DES UNICODE
• SQL SERVER 2000 - VIDER ET COMPACTER TOUTES LES BASES DE DONNÉES DU SERVEUR
• SQL SERVER 2005 : OBTENIR LA LISTE DES BASES DE CONTENU D'UNE FERME SHAREPOINT 2007
• SQL SERVER - OBTENIR LA POSITION DU PREMIER CHIFFRE D'UNE CHAINE DE CHARACTERES
• SQL SERVER - UPDATE D'UNE TABLE EN BOUCLE PAR LOT

• CRÉATION D'UNE BASE DE DONNÉE AVEC FICHIER DE DONNÉES, JOURNAL, TABLES ET CONSTRAINTES
• MYSQL : CRÉATION D'UN GUID
• SQL SERVER 2005 : OBTENIR LA LISTE DES BASES DE CONTENU D'UNE FERME SHAREPOINT 2007
• LABYRINTHE SUR SQL SERVER
• MASTERMIND SUR SQL SERVER

• MASTERMIND SUR SQL SERVER
• SQL SERVER - RESTAURER UNE BASE DEPUIS UN BACKUP
• MYSQL - MISE EN PLACE D'UN BACKUP AUTOMATIQUE DES BASES DE DONNÉES
• BACKUP DU FICHIER LOG
• SQL SERVER - PROCÉDURE STOCKÉE METTANT À JOUR TOUTES LES STATISTIQUES DE TOUTES LES BASES DU SERVEUR