1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
| 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 |
