La présente procédure permet d’effectuer des sauvegardes complètes ou différentielles et assure la suppression des fichiers de sauvegarde trop anciens.
1 - La procédureCREATE PROCEDURE S_MAINT.P_SAVE_DB
@DBNAME SYSNAME, -- nom de la base à sauvegarder
@PATH NVARCHAR(256), -- chemin de stockage des fichiers de sauvegarde
@FULL BIT = 1, -- 1 = complète, 0 = différentielle (complète à défaut)
@RETENTION_DAYS INT = 7 -- limite de rétention des sauvegardes en profondeur de jours
AS
/******************************************************************************
* Maintenance basique : réalisation de sauvegardes *
*******************************************************************************
* Frédéric Brouard - SQLpro - MVP SQL Server - www.sqlspot.com - 2010-03-24 *
*******************************************************************************
* Cette procédure exécute une sauvegarde complète ou différentielle et la *
* stocke dans le répertoire passé en argument *
******************************************************************************/
-- la base de données exite-elle ?
IF NOT EXISTS(SELECT *
FROM sys.databases
WHERE name = @DBNAME)
BEGIN
RAISERROR('La base de données %s n''existe pas sur ce serveur.', 16, 1, @DBNAME);
RETURN;
END;
-- le répertoire existe-il ou est-il atteignable ?
DECLARE @OSCMD NVARCHAR(2048);
SET @PATH = @PATH
+ CASE
WHEN SUBSTRING(@PATH, LEN(@PATH), 1) = '\'
THEN ''
ELSE '\'
END;
DECLARE @TF TABLE (DONE BIT, FNAME VARCHAR(256), FDATE DATETIME, FSIZE BIGINT);
INSERT INTO @TF (FNAME, FDATE, FSIZE)
EXEC S_MAINT.P_FILES @PATH;
IF @@ERROR <> 0
BEGIN
RAISERROR('Le répertoire %s n''existe pas sur ce serveur ou est inaccessible.', 16, 1, @PATH);
RETURN;
END;
-- tout est prêt, exécution de la sauvegarde
DECLARE @SQL NVARCHAR(max)
SET @SQL = 'BACKUP DATABASE [' + @DBNAME + '] TO DISK = ''' + @PATH
+ S_MAINT.F_RESCRIT(@DBNAME,
'- _.àáâãäçèéêëìíîïñòóôõöùúûüýabcçdefghijklmnopqrstuvwxyzÀÁÂÃÄÇÈÉÊËÌÍÎÏÑÒÓÔÕÖÙÚÛÜÝABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
'___.AAAAACEEEEIIIINOOOOOUUUUYABCCDEFGHIJKLMNOPQRSTUVWXYZAAAAACEEEEIIIINOOOOOUUUUYABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
'- _')
+ '_' + REPLACE(REPLACE(CONVERT(VARCHAR(19), CURRENT_TIMESTAMP, 126), '-', ''), ':', '')
+ '.BAK'''
+ CASE @FULL
WHEN 1 THEN ';'
WHEN 0 THEN ' WITH DIFFERENTIAL;'
END;
BEGIN TRY
EXEC (@SQL);
END TRY
BEGIN CATCH
DECLARE @MSG NVARCHAR(2048);
SET @MSG = ERROR_MESSAGE();
RAISERROR('Echec de sauvegarde de la base %s vers le répertoire %s : %s', 16, 1, @DBNAME, @PATH, @MSG);
RETURN;
END CATCH
-- vidage des fichiers trop anciens
DECLARE @FNAME VARCHAR(256);
UPDATE @TF
SET DONE = 0
WHERE FDATE < DATEADD(DAY, @RETENTION_DAYS * -1, CURRENT_TIMESTAMP);
WHILE EXISTS(SELECT *
FROM @TF
WHERE DONE = 0)
BEGIN
SELECT TOP 1 @FNAME = FNAME
FROM @TF
WHERE DONE = 0;
SET @OSCMD = 'DEL "' + @PATH + @FNAME + '"';
EXEC xp_cmdshell @OSCMD;
UPDATE @TF
SET DONE = 1
WHERE FNAME = @FNAME;
END
GO
NOTEZ que cette procédure utilise une procédure et une fonction :
la procédure S_MAINT.P_FILES qu liste les fichiers d’un répertoire de Windows 2003 version FR dont vous trouverez le code ici : http://blog.developpez.com/sqlpro/p8748/ms-sql-server/procedure-de-recuperation-des-fichiers-d/
la fonction S_MAINT.F_RESCRIT qui débarrasse un littéral de caractères parasite et dont vous trouverez le code ici : http://blog.developpez.com/sqlpro/p7392/langage-sql-norme/qualite-des-donnees-une-fonction-de-corr/
Et comme vous le constaterez tout ceci a été créé dans un schéma SQL de nom S_MAINT (CREATE S_MAINT).
2 – Exécution
Planifiez l’exécution de cette procédure aux heures creuses avec l’agent SQL Server, par exemple en créant une étape de travail par base à sauvegarder.
Voici par exemple la commande à lancer pour sauvegarder en mode complet une base de nom « BaseData » avec stockage dans le répertoire « C:\MesSauvegardesSQL\ » et rétention de 3 jours :
EXEC S_MAINT.P_SAVE_DB 'BaseData', 'C:\MesSauvegardesSQL\', 1, 3;
--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *
