Sauvegarde et copie distante [Résolu]

[Jinroh77]

Bonjour à tous,
Je suis en train de mettre en place un système de maintenance sur un serveur comportant plusieurs instance MS SQL Server.
J'ai commencé à utiliser les plans de maintenance intégré puis je suis finalement parti sur un véritable package SSIS qui me permet entre autre d'utiliser des tâches de copie de fichiers.


J'opère donc mes maintenances, sauvegardes complètes et des journaux de transaction dans les répertoires par défaut de chacune des instances.
Une tâche de nettoyage s'occupe de virer les .bak vieux de plus de 3 jours et les .trn de la journée.
Il faut ensuite que je déplace ces fichiers, notamment sur un serveur différent afin de prévenir d'un crash complet du serveur de base de données.

C'est là que les questions commencent...
Est-ce bien à chaque processus (full, système, journaux * toutes les instances) de copier ses fichiers vers un répertoire distant ?

Comment opérer le nettoyage des fichiers sur le répertoire distant pour conserver les .bak :
- des 5 derniers jours
- du dernier dimanche pendant 1 mois.

Est-ce que je centralise les fichiers de sauvegarde vers un répertoire globale sur le serveur de DB pour ensuite mettre en place le processus de copie vers l'autre serveur + suppression des sauvegardes hors date ?

Merci pour vos idées et bonnes pratiques.

[elsuket]

Bonjour,

st-ce bien à chaque processus (full, système, journaux * toutes les instances) de copier ses fichiers vers un répertoire distant ?

Malheureusement je n'ai jamais eu l'occasion d'utiliser des plans de maintenance pour effectuer des sauvegardes.

Ce que je sais c'est qu'en utilisant des instruction T-SQL, vous pouvez effectuer un BACKUP en copiant les fichiers de sauvegarde sur un autre serveur en utilisant la clause MIRROR TO.
Je ne sais pas si cela est possible dans un plan de maintenance.

Comment opérer le nettoyage des fichiers sur le répertoire distant pour conserver les .bak :
- des 5 derniers jours
- du dernier dimanche pendant 1 mois.

De ce que j'ai pu lire sur les plans de maintenance, il n'est pas possible d'utiliser des variables dans les packages SSIS sous-jacents aux plans de maintenance, donc vous ne pouvez pas faire calculer au package la nom du fichier qui doit être supprimé.

Le mieux est donc de créer une tâche d'exécution de lot SQL dans lequel vous appelez une procédure stockée.
Cette procédure stockée interroge la table d'historique des sauvegardes de bases de données : c'est dbo.backupset dans la base de données système msdb.
Intéressez vous notamment aux colonnes database_name, backup_start_date et backup_finish_date, qui vous permettront de trouver les bons noms de fichiers de sauvegardes.
Vous serez ensuite obligé d'appeler la procédure stockée étendue xp_cmdshell pour supprimer le fichier à l'aide de la commande DOS DEL.

Sinon vous pouvez écrire une procédure stockée d'assembly qui se charge de faire cela pour vous, et appelez là dans votre package SSIS, de la même façon.

Malheureusement cela fait plus d'un objet à copier pour votre plan de maintenance standard ...

@++

[Jinroh77]

En fait des plans de maintenance, je suis passé à de véritable packages SSIS qui utilisent seulement les tâches de maintenance. Je profite donc de toute la puissance des packages SSIS.

Merci pour l'idée des tables systèmes d'enregistrement de backup. J'étais d'ailleurs tombé sur ce billet qui les utilise et j'allais m'en inspirer.

SI vous n'utilisez que des scripts et non des tâches SSIS de maintenance, même de cette manière, qu'elle est la politique, un peu générale que vous utilisez ?
Vous utilisez la commande backup avec un MIRROR TO pour déplacer les fichiers sur un autre serveur ?
Ou est-ce un agent extérieur qui s'occupe du déplacement, nettoyage etc. ?


Merci.

[Christianbt]

Bonjour,

Si vous utilisez toutes les fonctionnalités de SSIS (et non pas seulement les outils liés au plan de maintenance), vous pouvez faire des scripts pour vos sauvegardes en pointant directement vers un "backup device" (périphérique de sauvegarde) qui va écrire vos fichiers .bak sur un serveur distant.
== backup database [ma_base] to [mon_peripherique] with...
Vous pouvez aussi utiliser les variables du package pour définir un nom à votre fichier .bak avec un compteur (date + heure par exemple si vous faites plusieurs sauvegardes quotidiennes).
Vous pouvez aussi définir une période de rétention à vos sauvegardes pour que sql server les suppriment automatiquement au bout de x jours.
Sinon, en fonction de la méthode que vous souhaitez mettre en place, vous pouvez faire appel à une procédure stockée qui va procéder à ces nettoyages.

Christian

[mikedavem]

Bonjour,

@Elsuket

Ce que je sais c'est qu'en utilisant des instruction T-SQL, vous pouvez effectuer un BACKUP en copiant les fichiers de sauvegarde sur un autre serveur en utilisant la clause MIRROR TO.
Je ne sais pas si cela est possible dans un plan de maintenance.

Non pas possible

@Jinroh77

C'est là que les questions commencent...
Est-ce bien à chaque processus (full, système, journaux * toutes les instances) de copier ses fichiers vers un répertoire distant ?

Il y a plusieurs façons de faire. Vous pouvez par exemple implémenter cette tâche dans votre package SSIS directement ou l'externaliser via une tâche planifiée et un script vbs ou powershell ...

Vous parlez de plusieurs instances sur un serveur mais votre package SSIS sera présent sur chaque serveur ou vous allez faire un package "centralisé" ?

++

[Jinroh77]

Envoyé par mikedavem

@Jinroh77

Il y a plusieurs façons de faire. Vous pouvez par exemple implémenter cette tâche dans votre package SSIS directement ou l'externaliser via une tâche planifiée et un script vbs ou powershell ...

Vous parlez de plusieurs instances sur un serveur mais votre package SSIS sera présent sur chaque serveur ou vous allez faire un package "centralisé" ?
++

J'ai créé un package par instance. aintenance pus compliquée mais au moins je peux préciser ce que je souhaite sauvegarder par instance.

Par contre, le package qui s'occupe de récupérer les sauvegardes, les déplacer, les nettoyer est lui, unique et déclenché par 1 seul job sur 1 instance que j'ai sélectionné.

[Jinroh77]

Finalement j'ai créé un nouveau package qui s'occupe du nettoyage de tous les fichiers de toutes les instances.
Celui-ci boucle sur une liste des instances et pour chacune il interroge les tables de la msdb pour récupérer tous les noms de fichier de backup sur un laps de temps.
Je filtre etc. pour ne garder que les fichiers que je souhaite supprimer (le tout est en 1 requête entièrement variabilisée dans mon package).

Ensuite pour chacun, il extrait le nom du fichier et le fait précéder du nom UNC de mon répertoire de backup. Cette liste passe enfin dans une boucle forEach avec un script C# qui pour chacun des paths, vérifie si le fichier existe puis le supprime.