Sauvegarder toutes les bases de données d’un serveur d’un seul coup

Voici une procédure destinée à opérer une sauvegarde globales de toutes les bases d’un serveur. Différentes options sont possible comme de sauvegarder aussi les bases systèmes ou utiliser une unité de sauvegarde (device).


CREATE PROCEDURE dbo.P_SAVE
   @FILE_PATH    NVARCHAR(256),             -- chemin pour stockage des fichiers de sauvegarde
   @FILE_EXT     NVARCHAR(3) = 'BAK',       -- extension pour les fichiers de sauvegarde
   @FILE_NAME    NVARCHAR(128) = 'DB_SAVE', -- nom générique des fichiers de sauvegarde
   @FILE_DATE    BIT = 1,                   -- incorporer la date dans le nom des fichiers
   @FILE_HOUR    BIT = 1,                   -- incorporer l'heure dans le nom des fichiers
   @ONLY_USER_DB BIT = 0,                   -- ne sauvegarder que les bases utilisateur (pas les bases systèmes)
   @TO_DEVICE    BIT = 1,                   -- créer une unité de sauvegarde (device) pour y inclure toutes les sauvegardes
   @CLEAN_DEVICE BIT = 1                    -- purger l'unité de sauvegarde (device) des sauvegrades qu'elle contient
AS
 
-- ATTENTION : les noms des bases de données doivent repecter la norme ISO (pas de caractères illicites)
-- dans le cas contraire prévoir les crochet dans le SQL dynamique
 
IF @FILE_PATH IS NULL OR @FILE_EXT IS NULL OR @FILE_NAME IS NULL
BEGIN
   SET @FILE_PATH = COALESCE(@FILE_PATH, '?');
   SET @FILE_EXT  = COALESCE(@FILE_EXT, '?');
   SET @FILE_NAME = COALESCE(@FILE_NAME, '?');
   RAISERROR ('Un des paramètres de la procédure est manquant. Chemin : %s - Nom fichier : %s - Extension : %s', 16, 1)
   RETURN
END;
 
-- la variable @PATH se termine t-elle par un "\" ?, si non, l'ajouter
IF SUBSTRING(REVERSE(@FILE_PATH), 1, 1) <> '\'
   SET @FILE_PATH = @FILE_PATH + '\';
-- l'extension ne doit pas avoir de point
SET @FILE_EXT = REPLACE(@FILE_EXT, '.', '');
 
 
-- le répertoire doit exister. S'il n'existe pas on le créé.
-- utilisation de xp_cmdshell
DECLARE @SHOW_OPTIONS BIT, @CMDSHELL BIT;
SELECT @SHOW_OPTIONS = CAST(value_in_use AS BIT)
FROM   sys.configurations
WHERE  name = 'show advanced options'
SELECT @CMDSHELL = CAST(value_in_use AS BIT)
FROM   sys.configurations
WHERE  name = 'xp_cmdshell'
IF @CMDSHELL = 0
BEGIN
   IF @SHOW_OPTIONS = 0
      EXEC sp_configure 'show advanced options', 1;
   EXEC sp_configure 'xp_cmdshell', 1;
   RECONFIGURE;
   IF @SHOW_OPTIONS = 0
      EXEC sp_configure 'show advanced options', 0;
   RECONFIGURE;
END
-- création du répertoire  
EXEC ('xp_cmdshell ''MKDIR "' + @FILE_PATH+'"''');
IF @CMDSHELL = 0
BEGIN
   EXEC sp_configure 'xp_cmdshell', 0;
   IF @SHOW_OPTIONS = 0
      EXEC sp_configure 'show advanced options', 0;
   RECONFIGURE;
END;
 
-- variables de travail
DECLARE @DATE NVARCHAR(9), @HOUR NVARCHAR(7),  
        @DEVICE_NAME NVARCHAR(128), @DEVICE_FILE NVARCHAR(512),
        @FILE_SAVE   NVARCHAR(512), @SQL VARCHAR(max);
 
-- conversion en chaine date et heure au format ISO court
IF @FILE_DATE = 1
   SET @DATE = REPLACE(CONVERT(NCHAR(10), CURRENT_TIMESTAMP, 121), '-', '');
IF @FILE_HOUR = 1
   SET @HOUR = SUBSTRING(REPLACE(CONVERT(NCHAR(25), CURRENT_TIMESTAMP, 121), ':', ''), 12, 6);
 
-- la sauvegarde se fait sur un device. Il faut le créer.
IF @TO_DEVICE = 1  
BEGIN
   SET @DEVICE_NAME = @FILE_NAME + COALESCE('_' + @DATE,'') + COALESCE('_' + @HOUR, '');
   SET @DEVICE_FILE = @FILE_PATH + @DEVICE_NAME + '.' +@FILE_EXT;
END;
 
IF @TO_DEVICE = 1 AND @CLEAN_DEVICE = 1
-- création d'un "device" (unité de sauvegarde) en fait un super fichier qui va contenir toutes les sauvegardes
   IF EXISTS (SELECT *  
              FROM   sys.backup_devices
              WHERE  name = @DEVICE_NAME)
      EXEC sp_dropdevice @DEVICE_NAME;
IF NOT EXISTS(SELECT *  
              FROM   sys.backup_devices
              WHERE  name = @DEVICE_NAME)
   EXEC sp_addumpdevice 'DISK', @DEVICE_NAME, @DEVICE_FILE;
 
-- génération de la commande SQL pour la liste des bases à sauvegarder
SET @SQL = '';
 
SELECT @SQL = @SQL + 'BACKUP DATABASE ' + name +' TO ' +  
-- vers device ou fichiers
       CASE  
          WHEN @TO_DEVICE = 1  
             THEN @DEVICE_NAME
          ELSE    'DISK = ''' + @FILE_PATH + @FILE_NAME + '_' + name +  
                    COALESCE('_' + @DATE,'') + COALESCE('_' + @HOUR, '')  +
                    COALESCE('.' + @FILE_EXT, '') + ''''
       END  +  
-- si device alors purger ou non
       CASE  
          WHEN @TO_DEVICE = 1 AND N = 1 AND @CLEAN_DEVICE = 1 THEN ' WITH FORMAT, INIT'
          ELSE ''
       END + ';'  + CHAR(10) + CHAR(13)
FROM   (SELECT name, ROW_NUMBER() OVER(ORDER BY database_id) AS N
        FROM   sys.databases  
        WHERE  state = 0
          AND  source_database_id IS NULL
          AND  (owner_sid <> 0x01 OR @ONLY_USER_DB <> 1)
          AND  name <> 'tempdb' ) AS T;
 
EXEC (@SQL);
 
GO

Exemple d’utilisation :


P_SAVE 'C:\! test sauve', 'bkp',     'SAUVEGARDE_SQL_SERVER', 1,          1,          0,             1,          0  
--     @FILE_PATH         @FILE_EXT   @FILE_NAME              @FILE_DATE  @FILE_HOUR  @ONLY_USER_DB  @TO_DEVICE  @CLEAN_DEVICE

***
Fr̩d̩ric BROUARD РSQLpro РMVP SQL Server
Spécialiste SQL/BD modélisation de données
SQL & SGBDR http://sqlpro.developpez.com/
Expert SQL Server : http://www.sqlspot.com
audits – optimisation – tuning – formation

Laisser un commentaire