En tant qu’administrateur de base de données (DBA), l’une des tâches les plus critiques est de s’assurer que les sauvegardes de la base de données sont effectuées régulièrement et qu’elles sont récupérables. Cependant, dans certains cas, vous n’avez peut-être pas un accès direct au serveur SQL ou le fournisseur peut restreindre votre accès en raison de réglementations de conformité telles que HIPAA. Dans de telles situations, il devient difficile de vérifier les sauvegardes et de garantir leur récupérabilité.

Dans cet article, nous discuterons d’une solution pour automatiser le processus de vérification des sauvegardes en utilisant PowerShell et SQL Server Agent. Cette solution vous permet de surveiller plusieurs instances de SQL Server et de générer un rapport quotidien indiquant l’état des sauvegardes.

Étape 1 : Créer des tables pour les propriétés des fichiers de sauvegarde

Pour commencer, nous devons créer une table dans une base de données pour stocker les propriétés des fichiers de sauvegarde. Cette table contiendra des informations telles que le nom du serveur, le partage de lecteur ou le dossier de sauvegarde, le chemin du fichier, le modèle de nom de fichier de sauvegarde et l’extension du fichier. Voici un exemple de création de la table :

USE [is_dba]
GO

CREATE TABLE [dbo].[is_nonsql_db](
 [is_server] [varchar](50) NOT NULL,
 [is_drive_letter] [varchar](50) NOT NULL,
 [is_path] [varchar](100) NULL,
 [is_start] [varchar](30) NOT NULL,
 [is_extension] [char](10) NOT NULL,
 [is_status] [char](1) NULL,
 [is_date_stamp] [smalldatetime] NULL,
 [is_identity] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX [IX_is_nonsql_db] ON [dbo].[is_nonsql_db] 
(
 [is_server] ASC,
 [is_identity] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
       DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Vous pouvez insérer des enregistrements dans cette table pour spécifier les serveurs et les propriétés des fichiers de sauvegarde à surveiller.

Étape 2 : Script PowerShell pour la vérification des sauvegardes

Ensuite, nous allons créer un script PowerShell qui lit les enregistrements de la table et vérifie la date de création du fichier de sauvegarde. Le script générera un rapport indiquant le nom du fichier, la date de modification et si le fichier se trouve dans les 24 dernières heures. Voici un exemple de script PowerShell :

[Exemple de script PowerShell]

Ce script utilise une connexion SQL Server pour récupérer les propriétés des fichiers de sauvegarde à partir de la table, puis vérifie l’existence et la date de modification des fichiers de sauvegarde. Le script génère un rapport texte avec les informations pertinentes.

Étape 3 : Automatiser la génération du rapport avec SQL Server Agent

Maintenant que nous avons le script PowerShell prêt, nous pouvons automatiser son exécution à l’aide de SQL Server Agent. Créez un nouveau travail dans SQL Server Agent et configurez-le pour exécuter le script PowerShell en tant qu’étape de travail de type PowerShell. Assurez-vous de spécifier le nom correct du serveur dans le script et veillez à ce que le travail s’exécute sous un compte DBA disposant des autorisations nécessaires pour accéder aux partages ou aux dossiers de sauvegarde.

Étape 4 : Examen des données du rapport

Une fois le travail configuré et planifié pour s’exécuter quotidiennement, il générera un fichier de rapport avec les résultats de vérification des sauvegardes. Le rapport inclura le nom du serveur, le nom du fichier, la date de modification et si le fichier se trouve dans les 24 dernières heures. Vous pouvez examiner ce rapport pour vous assurer que les sauvegardes sont effectuées correctement.

Étape 5 : Accès au rapport

Pour accéder au rapport, vous avez deux options. Vous pouvez soit créer un raccourci sur votre bureau vers le fichier de rapport, soit configurer une autre étape de travail de SQL Server Agent pour vous envoyer le rapport par e-mail. Si vous choisissez l’option d’e-mail, vous devez avoir la messagerie de base de données configurée dans SQL Server et utiliser la procédure stockée sp_send_dbmail pour envoyer le rapport en tant que pièce jointe.

EXEC sp_send_dbmail 
  @profile_name='DBA',
  @recipients='[email protected]',
  @subject='Rapport des sauvegardes Outcast DB',
  @body='Le fichier joint montre les bases de données SQL qui ont augmenté depuis hier',
  @file_attachments='E:\SSIS\is_non_sql\ps_backups_file_list.txt'

En suivant ces étapes, vous pouvez automatiser le processus de vérification des sauvegardes et vous assurer que vos bases de données sont protégées contre la perte de données. Cette solution est particulièrement utile lorsque vous n’avez pas un accès direct au serveur SQL ou lorsque vous gérez plusieurs serveurs avec des ressources limitées.

N’oubliez pas que la vérification régulière des sauvegardes est cruciale pour maintenir l’intégrité des données et répondre aux exigences de conformité. Avec cette approche automatisée, vous pouvez gagner du temps et garantir la récupérabilité de vos bases de données SQL Server.