begin process at 2008 07 06 07:12:30
1 205 472 membres
51 nouveaux aujourd'hui
14 119 membres club

Vous ne trouvez pas de réponse à votre problème ? Alors posez la question dans le forum.
Souvenez-vous qu'il n'y a jamais de question bête, mais rester dans l'ignorance parce que l'on n'ose pas poser une question, ça c'est une erreur !

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


Information sur la source

Catégorie :Divers Classé sous : sqlserver, procedure, backup, sauvegarde, journaux Niveau : Expert Date de création : 18/02/2005 Vu : 11 926

Note :
9 / 10 - par 1 personne
9,00 / 10

  • 1

  • 2

  • 3

  • 4

  • 5

  • 6

  • 7

  • 8

  • 9

  • 10

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


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___)
  • signaler à un administrateur
    Commentaire de dhardy le 17/10/2005 14:27:50

    Bonjour Fabrice,

    Aurais-tu le même type de procédure au niveau de la restauration ?
    Je dois faire des transferts d'un base de données réseau vers un portable. La procédure de sauvegarde m'est donc très utile mais je dois la restaurer sur un base de données sur un portable.

    Merci
    PS : mon adresse e-mail est dha@sigroup.be pour plus de facilité.

  • signaler à un administrateur
    Commentaire de fabrice69 le 17/10/2005 14:59:11 administrateur CS

    Bonjour,

    Vous devez utiliser la commande RESTORE, tel que l'exemple ci dessous :
    ---------------------------------------
    RESTORE FILELISTONLY
       FROM DISK = 'c:\Northwind.bak'
    GO
    ---------------------------------------
    > Cette commande restaure la base dans sa configuration d'origine (répertoires pour les fichiers de Log et Base)

    ---------------------------------------
    RESTORE DATABASE TestDB
       FROM DISK = 'c:\Northwind.bak'
       WITH MOVE 'Northwind' TO 'c:\test\testdb.mdf',
       MOVE 'Northwind_log' TO 'c:\test\testdb.ldf'
    GO
    ---------------------------------------
    > Cette commande restaure la base en spécifiant un chemin donné pour le fichier de base et un autre pour le fichier de Log.

    Cordialement
    Romelard Fabrice.

  • signaler à un administrateur
    Commentaire de nourwahiba le 17/04/2007 14:48:09

    bonjour

    je trouve que cette procedure est trés compliquée( au moin pour moi)
    alors si je veux faire un backup juste a une base de donnée et non pas a toutes les bases de sql quoi changer exactement
    merci pour vous

  • signaler à un administrateur
    Commentaire de fabrice69 le 17/04/2007 15:24:44 administrateur CS

    Bonjour,

    La commande de backup est la suivante :
    - ' BACKUP DATABASE '+ @name +' TO DISK ='''+ @Repertoire + @name +'.BAK'''

    Cordialement
    Romelard Fabrice [MVP]

  • signaler à un administrateur
    Commentaire de Womby le 27/04/2007 15:53:45

    Très pratique et instructif. Merci :)

  • signaler à un administrateur
    Commentaire de nourwahiba le 13/05/2007 11:43:07

    salam alikoum
    merci a vous  fabrice69
    alors j'ai mon application vb qui accede a une base de donnée sql server
    a partir de mon application je fais un backup :
    j'ai cree une procedure stockée en sql server qui a a comme parametre le nom de la base et  le chemin :

    CREATE PROCEDURE BackupTeste

    @base as varchar(800),
    @chemin  nvarchar(1000)
    AS
    BACKUP DATABASE @base
    To DISK = @chemin
    GO

    lorsque j'execute mon application sur mon server ( server2005)ca marche tres bien mais quand je l'execute sous reseau( un autre pc(portable2005) sous reseau qui a aussi le sql serveur ça marche pas meme je fais arreté le server portable2005 pour garder que le server2005 mais  ça marche pas ) comment  faire un backup sous reseau svp et merci pour votre aide

    bonne suite

  • signaler à un administrateur
    Commentaire de simbole12 le 15/01/2008 04:28:32

    slt a ts..
    j'ai 1 problem sur le backup d'1 fichier journale sous sql server,voila les codes que j'ai fait apré bien sur le buckup complete:
    use master
    backup log nom_da_la_base to unite2 with noinit
    et voila le message d'erreur:
    Serveur : Msg 4208, Niveau 16, État 1, Ligne 2
    L'instruction BACKUP LOG n'est pas autorisée lorsque le modèle de récupération est SIMPLE. Faites appel à BACKUP DATABASE ou modifiez le modèle de récupération au moyen de sp_dboption.
    Serveur : Msg 3013, Niveau 16, État 1, Ligne 2
    BACKUP LOG s'est terminé anormalement.
    merci pr vetre aide

  • signaler à un administrateur
    Commentaire de fabrice69 le 15/01/2008 09:43:21 administrateur CS

    Bonjour,
    Le mode "Simple" de journalisation ne fait pas bouger le fichier des logs de transaction.
    De ce fait le backup des log ne peut pas se faire.
    Je vous invite à lire la documentation sur le sujet :
    - http://sql.developpez.com/sqlserver/log/
    Cordialement
    Romelard Fabrice

  • signaler à un administrateur
    Commentaire de nourwahiba le 15/01/2008 11:04:45

    bonjour tout le monde
    a vous  fabrice69
    est ce qui'l est possible de protéger ma base de données sous sql server ..je m'explique ...j'ai installer sql server  dans un PC SERVER  jai ma base "COMMERCAILE08"    il ya 3 personnes qui ont le mot de passe du server  alors comment faire pour les empécher d'acceder carement a entreprise manager ou bien a ma ma base et la consulter

    merci a vous tous et bon courage

  • signaler à un administrateur
    Commentaire de fabrice69 le 15/01/2008 13:46:58 administrateur CS

    Bonjour,
    Ce message n'a aucun rapport avec le sujet de cette source.
    Je vous invite à poser votre question sur le forum adéquat.
    Cordialement
    Romelard Fabrice

  • signaler à un administrateur
    Commentaire de AMEDTIAB le 24/02/2008 11:01:10

    bonjour
    la procédure proposée est très instructive et je vous en remercie à titre indivuel, je commence dans le transact SQL et c'est pour moi une veritable bibliothèque.

Ajouter un commentaire

Pub



Appels d'offres

Plugin Dialer outlook
Budget : 2 000€
Travail graphique- ill...
Budget : 1 000€
creation de marque et ...
Budget : 1 000€

CalendriCode

Juillet 2008
LMMJVSD
 123456
78910111213
14151617181920
21222324252627
28293031   

Téléchargements

Logiciels à télécharger sur le même thème :

Boutique

Boutique de goodies CodeS-SourceS