Article complet: 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).

[Suite:]

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