Une procédure pour sauvegarder toutes vos bases de données

Vous en rêviez ? Je l’ai faite pour vous… De quoi vous simplifier la vie avec une procédure pour sauvegarder toutes vos bases de données d’un seule coup !

Cette procédure permet de sauvegarder toutes les bases d’une instance en choisissant d’exclure :
1) les bases systèmes master, model et msdb (tempdb n’est pas sauvegardable)
[paramètre @ONLY_USER_DB bit]
2) d’exclure une liste de base de votre choix, données sous la forme d’un document XML dont la forme est contrôlée
[paramètre @EXCLUDE_DB xml]

Et d’indiquer différents autres paramètres tels que :

  • Le type de sauvegarde qui peut être complète (valeur F), différentielle (valeur D) ou transactionnelle (valeur T)
  • [paramètre @BACKUP_TYPE char(1)]

  • Le chemin de sauvegarde (vérifié)
  • [paramètre @FILE_PATH nvarchar(256)]

  • Le nom du fichier qui, s’il n’est pas NULL sera la destination de toutes les sauvegardes
  • [paramètre @FILE_NAME nvarchar(128)]

  • L’extension du nom du fichier de sauvegarde qui, s’il est NULL sera .BAK (pour complète), .DIF (pour différentielle) .TRN (pour transactionnelle)
  • [paramètre @FILE_EXT nvarchar(8)]

  • S’il convient que le nom du fichier contienne la date et/ou l’heure
  • [paramètre @FILE_DATE bit]
    [paramètre @FILE_TIME bit]

  • Dans le cas ou la date et l’heure doivent figurer dans le fichier, savoir si c’est en heure locale ou UTC (dans ce dernier cas, le nom se terminera par UTC)
  • [paramètre @UTC_DATETIME bit]

  • S’il convient de compresser les sauvegardes
  • [paramètre @COMPRESSION]

  • S’il faut écraser l’ancien fichier de même nom
  • [paramètre @FILE_FORMAT bit]

    En voici le code, pleinement documenté :

    CREATE PROCEDURE dbo.P_BACKUP_ALL_DATABASE 
       @BACKUP_TYPE  CHAR(1),                   -- type of backup F for FULL, D for Differential, T for Logs (transactional)
       @FILE_PATH    NVARCHAR(256),             -- path to store the backups. If NULL store the backups in the défault backup path
       @FILE_EXT     NVARCHAR(8)        ,       -- if NULL, BAK for Full, DIF for Differential and TRN for Logs (transactions)
       @FILE_NAME    NVARCHAR(128),             -- if NULL create one file per database to be backuped, if valued stacks all the backups in only one nammed file
       @FILE_DATE    BIT,                       -- if 1 add date in file name
       @FILE_TIME    BIT,                       -- if 1 add time in file name
       @FILE_FORMAT  BIT,                       -- if 1 format the existing file
       @UTC_DATETIME BIT,                       -- if 0 datetime is in local time (by default, UTC datetime)
       @ONLY_USER_DB BIT,                       -- if 1 backup only user's databases
       @EXCLUDE_DB   XML,                       -- list of databases to exclude in the form of '<dbs><db>database_name1</db><db>database_name2</db></dbs>'
       @COMPRESSION  BIT                        -- if one add compression  
    AS 
    /******************************************************************************
    * MODULE : BACKUP                                                             *
    * NATURE : PROCEDURE                                                          *
    * OBJECT : dbo.P_BACKUP_ALL_DATABASE                                          *
    * CREATE : 2018-05-29                                                         *
    * VERSION : 1                                                                 *
    *******************************************************************************
    * Frédéric BROUARD  -  alias SQLpro  -   SARL SQL SPOT  -  SQLpro@sqlspot.com *
    * Architecte de données :  expertise, audit, conseil, formation, modélisation *
    * tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL *
    * blog: http://mssqlserver.fr           website: http://sqlpro.developpez.com *
    * Most Valuable Professional  - MVP -  SQL Server (data platform) depuis 2004 *
    *******************************************************************************
    * PURPOSE : backup all databases in an instance                               *
    *******************************************************************************
    * INPUTS :                                                                    *
    *  @BACKUP_TYPE  CHAR(1)         -- type of backup F for FULL, D for          *
    *                                -- Differential, T for Logs (transactional)  *
    *  @FILE_PATH    NVARCHAR(256)   -- path to store the backups. If NULL store  *
    *                                -- the backups in the défault backup path    *
    *  @FILE_EXT     NVARCHAR(8)     -- if NULL BAK for Full DIF for Differential *
    *                                -- and TRN for Logs (transactions)           *
    *  @FILE_NAME    NVARCHAR(128)   -- if NULL create one file per database to   *
    *                                -- be backuped, if valued stacks all the     *
    *                                -- backups in the only file you nammed       *
    *  @FILE_DATE    BIT             -- if 1 add date in file name                *
    *  @FILE_TIME    BIT             -- if 1 add time in file name                *
    *  @FILE_FORMAT  BIT             -- if 1 format the existing file             *
    *  @UTC_DATETIME BIT             -- if 0 datetime is in local time, if 1 UTC  *
    *  @ONLY_USER_DB BIT             -- if 1 backup only user's databases         *
    *  @EXCLUDE_DB   XML             -- list of databases to exclude. Patterne is *
    *                                   '<dbs>                                    *
    *                                       <db>database_name1</db>               *
    *                                       <db>database_name2</db>               *
    *                                       ...                                   *
    *                                    </dbs>'                                  *
    *  @COMPRESSION  BIT             -- if one add compression                    *
    *******************************************************************************
    * OUPUT : none                                                                *
    *******************************************************************************
    * RUNNABLE : version 2012 and superior                                        *
    *            must have sysdamin role or equivalent (for of logging errors)    *
    *******************************************************************************
    * SAMPLE :                                                                    *
    *    EXEC dbo.P_BACKUP_ALL_DATABASE                                           *
    *       @BACKUP_TYPE  = 'F',                                                  *
    *       @FILE_PATH    = 'C:\DATA',                                            *
    *       @FILE_EXT     = NULL,                                                 *
    *       @FILE_NAME    = NULL,                                                 *
    *       @FILE_DATE    = 1,                                                    *
    *       @FILE_TIME    = 1,                                                    *
    *       @FILE_FORMAT  = 1,                                                    *
    *       @UTC_DATETIME = 1,                                                    *
    *       @ONLY_USER_DB = 1,                                                    *
    *       @EXCLUDE_DB   = N'<dbs>                                               *
    *                          <db>ReportServer$SQL2016FBIN2</db>                 *
    *                          <db>ReportServer$SQL2016FBIN2TempDB</db>           *
    *                         </dbs>',                                            *
    *       @COMPRESSION = 1                                                      *
    ******************************************************************************/
    SET NOCOUNT ON;
    
    -- verify if @BACKUP_TYPE is F, D or T
    IF @BACKUP_TYPE COLLATE Latin1_General_CI_AS NOT IN ('F', 'D', 'T')
    BEGIN
       RAISERROR('The backup type "%s" is not a valid one. Must be "F" for FULL, "D" for Differentiel or "T" for Transactional (logs).', 16, 1, @BACKUP_TYPE);
       RETURN;
    END;
    
    --verify if @FILE_EXT is composed only of letters, figures and underscore
    IF NOT @FILE_EXT COLLATE Latin1_General_CI_AS LIKE REPLICATE('[A-Z0-9_]', LEN(@FILE_EXT)) OR @FILE_EXT = ''
    BEGIN
       RAISERROR('The file extension %s is not a valid one. Must be composed of letters, figure and underscore.', 16, 1, @FILE_EXT);
       RETURN;
    END;
    
    --verify if @FILE_EXT is composed only of letters, figures and underscore
    IF NOT @FILE_NAME COLLATE Latin1_General_CI_AS LIKE REPLICATE('[A-Z0-9_]', LEN(@FILE_NAME)) OR @FILE_NAME = ''
    BEGIN
       RAISERROR('The file name %s is not a valid one. Must be composed of letters, figure and underscore.', 16, 1, @FILE_NAME);
       RETURN;
    END;
    
    -- verify if directory exists
    IF @FILE_PATH IS NOT NULL
    BEGIN
       DECLARE @TF TABLE (file_exists BIT, file_is_dir BIT, parent_file BIT);
       INSERT INTO @TF
       EXEC xp_fileexist @FILE_PATH;
       IF NOT EXISTS(SELECT * FROM @TF WHERE file_is_dir = 1)
       BEGIN
          RAISERROR('The directory %s must exists', 16, 1, @FILE_PATH);
          RETURN;
       END;
    END
    
    -- add backslash at the end if not exists
    IF RIGHT(@FILE_PATH, 1) <> CHAR(92)
       SET @FILE_PATH = @FILE_PATH + CHAR(92);
    
    -- verify the XML syntax of the database list to exclude
    IF @EXCLUDE_DB IS NOT NULL
    BEGIN
       BEGIN TRANSACTION;
       BEGIN TRY
          DECLARE @XQL NVARCHAR(max);
          SET @XQL = 'USE msdb;CREATE XML SCHEMA COLLECTION dbo.XSC_3A586931_C43F_47C1_A419_6E19DB23CEC3 
          AS N''<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
            <xs:element name="dbs">
              <xs:complexType>
                <xs:sequence>
                  <xs:element type="xs:string" name="db" maxOccurs="unbounded" minOccurs="1"/>
                </xs:sequence>
              </xs:complexType>
            </xs:element>
          </xs:schema>'';'
          EXEC (@XQL);
          SET @XQL = N'USE msdb;CREATE TABLE dbo.XTB_3A586931_C43F_47C1_A419_6E19DB23CEC3 (data_bases XML (XSC_3A586931_C43F_47C1_A419_6E19DB23CEC3));'
          EXEC (@XQL)
          SET @XQL = N'USE msdb;INSERT INTO dbo.XTB_3A586931_C43F_47C1_A419_6E19DB23CEC3 VALUES (''' + CAST(@EXCLUDE_DB AS varchar(max)) + N''');'
          EXEC (@XQL);
          ROLLBACK;
       END TRY
       BEGIN CATCH
          IF XACT_STATE() <> 0
             ROLLBACK;
          THROW 55555, 'XML list of databases to exclude is in an uncorrect format. Must be formed like "<dbs><db>database_name1</db><db>database_name2</db></dbs>".', 1;
          RETURN;
       END CATCH;
    END;
    
    -- local variables
    DECLARE @dbid     INT,             -- the database id
            @dbname   sysname,         -- the name of the database
            @f_name   sysname,         -- file name for backup  
            @NEWNAME  sysname,         -- corrected file name
            @I        TINYINT,         -- loop indice
            @SQL      NVARCHAR(max),   -- SQL code to be dynamically executed
            @DT       DATETIME,        -- system datetime    
            @D        NCHAR(9),        -- backup date encoded as aaaammjjD (like 20181231D)
            @T        NCHAR(9),        -- backup time encoded as hhHmmMssS (like 21H38M05S)    
            @ERRORS   SMALLINT,        -- number of errors
            @ERRMSG   VARCHAR(1024);   -- error message   
    
    -- find the candidate databases
    CREATE TABLE #dbs (database_id INT PRIMARY KEY, dbname sysname, f_name NVARCHAR(128));
    INSERT INTO #dbs (database_id, dbname)
    SELECT database_id, name
    FROM   sys.databases 
    WHERE  state = 0
      AND  source_database_id IS NULL       -- exclude snapshot database
      AND  name NOT IN ('tempdb')           -- exclude tempdb
      AND  CASE WHEN @BACKUP_TYPE = 'T'     -- transactional backup only if recovery model <> SIMPLE
                     AND recovery_model = 3 
                   THEN 0
                ELSE 1 
           END = 1
      AND  CASE WHEN @ONLY_USER_DB = 1      -- excluding system database if required
                     AND name IN ('master', 'msdb', 'model') 
                   THEN 0
                ELSE 1 
           END = 1                          -- excluding database from the XML list                              
      AND  name NOT IN (SELECT db_name.value('(.)[1]', 'sysname')
                        FROM   @EXCLUDE_DB.nodes('/dbs/db') AS TX(db_name));
    
    -- correcting file name to have only letters without accents
    UPDATE #dbs
    SET    f_name = CAST(dbname collate SQL_Latin1_General_Cp1251_CS_AS AS varchar(128));
    
    -- correcting file name with only letters (A to Z), figures (0 to 9) and underscore (_)
    DECLARE C1 CURSOR 
       LOCAL FORWARD_ONLY 
    FOR SELECT database_id, f_name
        FROM   #dbs
        FOR UPDATE OF f_name;
    OPEN C1;
    FETCH C1 INTO @dbid, @f_name;
    WHILE @@FETCH_STATUS = 0
    BEGIN
       SELECT @I = 1, @NEWNAME = N'';
       WHILE @I <= LEN(@f_name)
       BEGIN
          IF SUBSTRING(@f_name, @I, 1) LIKE '[A-Z0-9_]' COLLATE Latin1_General_CI_AS 
             SET @NEWNAME = @NEWNAME + SUBSTRING(@f_name, @I, 1);
          ELSE
             SET @NEWNAME = @NEWNAME + N'_';
          SET @I = @I + 1;
       END
       UPDATE #dbs
       SET f_name = @NEWNAME
       WHERE CURRENT OF C1;
       FETCH C1 INTO @dbid, @f_name; 
    END
    CLOSE C1;
    DEALLOCATE C1;
    
    SET @ERRORS = 0;
    
    -- we use cursor to backup databases one by one, which facilitate the rollback 
    DECLARE C2 CURSOR 
       LOCAL FORWARD_ONLY STATIC READ_ONLY 
    FOR SELECT dbname, f_name
        FROM   #dbs;
    OPEN C2;
    FETCH C2 INTO @dbname, @f_name;
    
    -- loop over the databases 
    WHILE @@FETCH_STATUS = 0
    BEGIN
       -- calculate date and time as strings if needed
       IF @FILE_DATE = 1 OR @FILE_TIME = 1
          SET @DT = CASE @UTC_DATETIME WHEN 0 THEN GETDATE() ELSE GETUTCDATE() END;
       IF @FILE_DATE = 1 AND @FILE_NAME IS NOT NULL AND @D IS NULL
          SET @D = CONVERT(CHAR(8), @DT, 112) + 'D';
       IF @FILE_TIME = 1 AND @FILE_NAME IS NOT NULL AND @T IS NULL
          SET @T = FORMAT(CONVERT(TIME, @DT, 108), 'hh\Hmm\Mss\S');
       -- compose the backup sql command
       SET @SQL = N'BACKUP ' 
                + CASE WHEN @BACKUP_TYPE = N'T' THEN N'LOG' ELSE N'DATABASE' END 
                + N' [' + @dbname + N'] TO DISK = ''' 
                +  COALESCE(@FILE_PATH, N'') 
                + CASE 
                     WHEN @FILE_NAME IS NOT NULL 
                        THEN @FILE_NAME
                        ELSE @f_name 
                  END
                + CASE WHEN @FILE_DATE = 1 THEN N'_' + @D ELSE '' END
                + CASE WHEN @FILE_TIME = 1 THEN N'_' + @T ELSE '' END
                + CASE WHEN @UTC_DATETIME = 1 THEN N'_UTC' ELSE '' END
                + N'.' 
                + CASE 
                     WHEN @FILE_EXT IS NOT NULL 
                        THEN @FILE_EXT 
                        ELSE
                          CASE @BACKUP_TYPE
                             WHEN 'F' THEN 'BAK'
                             WHEN 'D' THEN 'DIF'
                             WHEN 'T' THEN 'TRN'
                             ELSE ''
                          END
                  END
                + N''' WITH ' 
                + CASE WHEN @FILE_FORMAT = 1 THEN N'FORMAT' ELSE N'NOFORMAT' END
                + CASE WHEN @BACKUP_TYPE = N'D' THEN N', DIFFERENTIAL' ELSE '' END
                + CASE WHEN @COMPRESSION = 1 THEN N', COMPRESSION' ELSE '' END
                + N';'
       -- execute the SQL backup command
       BEGIN TRY
          EXEC (@SQL); 
       END TRY
       BEGIN CATCH
       -- write a log message if in error
          SET @ERRMSG = ERROR_MESSAGE();
          RAISERROR ('>> BACKUP: %s (SQL command: %s).', 16, 1, @ERRMSG, @SQL) WITH LOG;
          SET @ERRORS = @ERRORS + 1;
          FETCH C2 INTO @dbname, @f_name;
          CONTINUE;
       END CATCH
    
       FETCH C2 INTO @dbname, @f_name;
    
    END;
    
    CLOSE C2;
    DEALLOCATE C2;
    
    IF @ERRORS >0
       THROW 55555, 'Almost one backup exception has been raised. Check SQL logs to find entries beginning with ">> BACKUP:".', 1;
     
    GO

    La forme XML du document pour exclure les bases doit être basé qui la modèle suivant :

    <dbs>
       <db><em>database_name1</em></db>
       <db><em>database_name2</em></db>
    </dbs>

    Fonctionne à partir de la version 2012 de SQL Server et nécessite un privilège ayant le rôle de sysadmin ou équivalent.

    En cas d’erreur, les exécutions ratées sont journalisée dans le journal d’événement de SQL Server.

    CONSEIL : créez la dans la base msdb et dans un schéma spécifique que vous allez créer pour l’occasion. Pour ma part j’utilise un schema de nom _MAINTENANCE dans msdb pour tous ces outils (notez le blanc souligné devant le nom… Hé oui, c’est permis !

    Exemple d’utilisation :

    EXEC dbo.P_BACKUP_ALL_DATABASE 
       @BACKUP_TYPE  = 'T', 
       @FILE_PATH   = NULL, 
       @FILE_EXT    = NULL,    
       @FILE_NAME   = 'mes_bases',         
       @FILE_DATE   = 1,      
       @FILE_TIME   = 1,  
       @FILE_FORMAT = 1,
       @UTC_DATETIME = 1,    
       @ONLY_USER_DB = 1,
       @EXCLUDE_DB = N'<dbs><db>ReportServer$SQL2016FBIN2</db><db>ReportServer$SQL2016FBIN2TempDB</db></dbs>',
       @COMPRESSION = 1

    Ceci effectue les sauvegardes transactionnelle compresséesde toutes les bases candidates à l’exception des bases systèmes, des base en mode de récupération simple et des bases « ReportServer.. » listé dans le XML d’exclusion et envoie le tout dans un seul fichier de nom « mes_bases_20180529D_14H02M58S_UTC.TRN » si la commande a été lancé le 29/5/2018 à 14h02 et 58s (en temps universel) et place le tout dans le répertoire par défaut des sauvegardes.

    Le lien de téléchargement du code


    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    * Le site sur les SGBD relationnels et le SQL : https://sqlpro.developpez.com *
    * le blog SQL, SQL Server, SGBDR... sur : https://blog.developpez.com/sqlpro/ *
    * Expert Microsoft SQL Server, MVP (Most valuable Professional) depuis 14 ans *
    * Entreprise SQL SPOT : modélisation, conseil, audit, optimisation, formation *
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

    Ce contenu a été publié dans Procédure de maintenance, Procédures MS SQL Server, avec comme mot(s)-clé(s) , . Vous pouvez le mettre en favoris avec ce permalien.