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