La sauvegarde de vos bases de données SQL Server est essentielle pour la protection des données et la récupération après sinistre. Cependant, la création manuelle de dispositifs de sauvegarde et le lancement de sauvegardes pour plusieurs bases de données peuvent être fastidieux et chronophages. Dans cet article, nous discuterons de la façon d’automatiser le processus de sauvegarde à l’aide de scripts T-SQL et de packages DTS.

Création de dispositifs de sauvegarde

Lors de la mise en place d’une politique de sauvegarde pour plusieurs bases de données, il est nécessaire de créer des dispositifs de sauvegarde pour chaque base de données. Cela peut être une tâche ardue si vous avez un grand nombre de bases de données. Pour rendre ce processus plus efficace, nous pouvons utiliser des scripts T-SQL pour générer le code nécessaire à la création des dispositifs de sauvegarde.

Par exemple, le code T-SQL suivant peut être utilisé pour créer des dispositifs de sauvegarde complets :

SELECT
'IF NOT EXISTS (SELECT name FROM master.dbo.sysdevices WHERE name = N' +  char(39) + name 
+  char(39) + ')' + ' EXEC sp_addumpdevice ' +  char(39) + 'disk' +  char(39) + ', ' 
+  char(39) + name +  char(39) + ', ' +  char(39) + '<DRIVE>:<PATH_OF_BACKUP_REPOSITORY>' 
+ name +'.bak' +  char(39) + ' ' as inst
FROM
sysdatabases
WHERE
name NOT IN ('master', 'model', 'msdb', 'tempdb')
ORDER BY
name

De même, le code T-SQL suivant peut être utilisé pour créer des dispositifs de sauvegarde transactionnels :

SELECT
'IF NOT EXISTS (SELECT name FROM master.dbo.sysdevices WHERE name = N' +  char(39) + 'TRAN_' 
+ name +  char(39) + ')' + ' EXEC sp_addumpdevice ' +  char(39) + 'disk' +  char(39) + ', ' 
+  char(39) + 'TRAN_' + name  +  char(39) + ', ' +  char(39) 
+ '<DRIVE>:<PATH_OF_BACKUP_REPOSITORY>' + 'TRAN_' + name +'.bak' +  char(39) +
' ' as inst
FROM
sysdatabases
WHERE
name NOT IN ('master', 'model', 'msdb', 'tempdb')
ORDER BY
name

Lancement des sauvegardes

Une fois les dispositifs de sauvegarde créés, nous pouvons utiliser des scripts T-SQL pour lancer les sauvegardes. Par exemple, le code T-SQL suivant peut être utilisé pour effectuer des sauvegardes complètes :

SELECT
'BACKUP DATABASE [' + name +  '] TO [' + name + '] WITH INIT, NOUNLOAD, NAME = N' 
+  char(39) + 'Admin - ' + name + ' backup' +  char(39) + ', SKIP, STATS = 10, FORMAT' as Inst
FROM
sysdatabases
WHERE
name NOT IN ('master', 'model', 'msdb', 'tempdb', 'GIP')
ORDER BY
name

De même, le code T-SQL suivant peut être utilisé pour effectuer des sauvegardes transactionnelles :

DECLARE @Date as varchar (30)
SET @Date = getdate()
SELECT
'BACKUP LOG [' + name +  '] TO [TRAN_' + name + '] WITH NOINIT, NOUNLOAD, NAME = N' +  char(39) 
+ 'Admin - ' + name + ' backup trans' +  char(39) + ', SKIP, STATS = 10, MEDIANAME = N' 
+  char(39) + 'set Transacional da bd' + name + char(39) + ', MEDIADESCRIPTION = N' +  char(39) 
+ @Date + char(39) as Inst_trn
FROM
sysdatabases
WHERE
name NOT IN ('master', 'model', 'msdb', 'tempdb')
ORDER BY
name

Automatisation du processus de sauvegarde

Après avoir créé les scripts T-SQL nécessaires, nous pouvons automatiser le processus de sauvegarde à l’aide de packages DTS et de tâches associées. Les packages DTS peuvent être programmés pour s’exécuter quotidiennement et générer les dispositifs de sauvegarde et le fichier d’instructions de sauvegarde. À l’intérieur de chaque tâche de transformation de données, un script peut être ajouté pour générer les sauvegardes complètes, des journaux de transactions et différentielles.

Une fois que les packages DTS génèrent les fichiers de sauvegarde, nous pouvons mettre en place des tâches pour exécuter ces fichiers. Par exemple, la tâche de sauvegarde complète peut être programmée pour s’exécuter quotidiennement à 7h00, la sauvegarde du journal transactionnel peut être configurée pour s’exécuter toutes les 15 minutes et la sauvegarde différentielle peut s’exécuter toutes les 4 heures.

Pour exécuter les sauvegardes, nous pouvons utiliser les commandes OSQL ou SQLCMD à l’intérieur des tâches des jobs. Par exemple, la commande suivante peut être utilisée pour exécuter les sauvegardes à l’aide d’OSQL :

osql -S <NOM DU SERVEUR> -E -h-1 -i <DRIVE>:\\.txt  
-o <DRIVE>:\\.rpt

De même, la commande suivante peut être utilisée pour exécuter les sauvegardes à l’aide de SQLCMD :

sqlcmd -S <NOM DU SERVEUR> -E -h-1 -i <DRIVE>:\\.txt 
-o <DRIVE>:\\.rpt

Conclusion

L’automatisation du processus de sauvegarde des bases de données SQL Server est essentielle pour garantir la protection des données et la récupération après sinistre. En utilisant des scripts T-SQL et des packages DTS, nous pouvons rationaliser le processus de sauvegarde et éliminer la nécessité d’interventions manuelles. N’oubliez pas de tester périodiquement l’intégrité de vos sauvegardes en effectuant des restaurations de test pour garantir leur fiabilité. La mise en place d’une solution de sauvegarde robuste et précise est une responsabilité essentielle pour les administrateurs de bases de données.