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 :
[paramètre @BACKUP_TYPE char(1)]
[paramètre @FILE_PATH nvarchar(256)]
[paramètre @FILE_NAME nvarchar(128)]
[paramètre @FILE_EXT nvarchar(8)]
[paramètre @FILE_DATE bit]
[paramètre @FILE_TIME bit]
[paramètre @UTC_DATETIME bit]
[paramètre @COMPRESSION]
[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 * * * * *