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

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