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___)
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.