-- à partir de la version 2012

DROP PROCEDURE dbo.P_BACKUP_ALL_DATABASE 
GO

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);

SELECT ASCII('\')

-- 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


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