Nettoyage d'un backup différentiel

Bonjour à tous.tes. C'est core mouai :?

Je ne sais pas si c'est possible, car je n'ai rien trouvé dans la doc SQLServer, ou je n'ai pas réussi à lire ce que je n'ai pas trouvé :roll:.

J'ai mis en place une sauvegarde différentielle.
Mon problème est que chaque fichier différentiel contenu dans le .bak contient toutes les nouvelles données depuis la sauvegarde INI.
Ben, ça fait que le .bak me mange vite mon disque dur.
N'y aurait-il pas un moyen de lui dire de supprimer les premiers fichiers différentiels, sans remettre en question la sauvegarde INI ? puisque INI + FILE dernier créé = restauration complète.
Du genre :

Code:

---

1 2 3 4 5

BACKUP DATABASE DBName TO DISK = '\\Database\DBName.Bak' WITH DIFFERENTIAL AND DELETE FILE 2

---

:lol:

Si quelqu'un a une idée, merci d'avance. :)

Au fait, j'ai lu quelque part dans le forum, que WITH COMPRESSION ne marche pas avec la version express. C'est vrai ?

La première question à se poser est : pourquoi faire des sauvegardes ?

Normalement on devrait trouver au moins un truc du genre "pour pouvoir faire des restaurations dans des délais et avec une "perte" de données acceptable".

Le problème avec la restauration est que c'est l'opération la plus dangereuse qu'il soit :weird:
Comme la première étape est de réinitialiser tous les fichiers, si ça se passe mal, on a vraiment tout perdu.
Donc on préfère garder plusieurs copies sous le coude car entre la théorie et la pratique il y a un salaire :(

Vu que t'es en version express la seule solution pour la planification sera de passer par le scheduler système
On peut trouver de bons scripts ici : https://ola.hallengren.com/

En adaptant le script tu pourras appliquer les suppressions qui vont bien.

Merci pour ton aide Michel, je vais m'intéresser à ce script, mais il faut que je puisse faire des sauvegardes différentielles avec...

Citation:

---

Envoyé par Michel.Priori

La première question à se poser est : pourquoi faire des sauvegardes ?
Donc on préfère garder plusieurs copies sous le coude car entre la théorie et la pratique il y a un salaire :(

---

Tout à fait, sauf qu'on a pas besoin de garder cinquante fichiers.

Pour supprimer les vieux backup, j'utilise un script Powershell en plus que ce qui existe déjà dans les scripts Ola. Et j'exécute via le task scheduler de Windows Server.

Tu as un ficher .txt qui s'appelle pathList.txt et je mets le dossier que je veux

Exemple :
\\server01\SQL\Bcp\
\\server02\SQL\Back\

Et un autre fichier que j'appelle deleteOldFiles.ps1

Code:

---

1 2 3 4 5 6 7 8

# Change the value $oldTime in order to set a limit for files to be deleted. $oldTime = [int]45 # 45 days foreach ($path in Get-Content "C:\DeleteOldBackupSQL\pathList.txt") { # Write information of what it is about to do Write-Host "Trying to delete files older than $oldTime days, in the folder $path" -ForegroundColor Green # deleting the old files - $_.CreationTime si on veut la date de création. ATTENTION car pour un copier/coller, la date est du jour alors Get-ChildItem $path -Recurse -Include "*.bak","*.dif","*.trn" | WHERE {($_.LastWriteTime -le $(Get-Date).AddDays(-$oldTime))} | Remove-Item -Force }

---

Je te conseille fortement de garder plusieurs backups et de les tester aussi régulièrement qu'il ne soit pas corrompu. L'idéal, c'est de faire un restore et de faire un dbcc checkdb ensuite, pour tes DB importantes.

Donc ne supprime pas trop vite tes DIFF, tu pourrais un jour le regretter.

Write-Host dans un script automatisé :)

Merci janlouk. Je mets de côté, au cas où...
Mais pour l'heure, je ne veux surtout pas supprimer mon fichier bak. J'aimerais juste supprimer les différentielles les plus anciennes contenues dans ce fichier bak, pour pouvoir continuer à faire des différentielles dans ce fichier sans qu'il grossisse démesurément.

Ce qui m'intéresse dans les diff c'est la rapidité d'exécution. Donc, si je suis obligé de faire une INI tous les jour, ça ne me sert à rien.
J'aimerais pouvoir faire une INI en début de semaine, et des diff toutes les heures pendant la semaine. Et tout cela sans intervention manuelle de ma part.

Ca ne pose aucun problème d'implémentation, mais au bout de deux jours, mon disque dur est saturé par la fichier bak qui est devenu énorme (évidemment).

Citation:

---

Envoyé par abdallah_mehdoini

Write-Host dans un script automatisé :)

---

J'aime bien car je suis toujours connecté sur ce serveur de management, donc je le vois se lancer. Mais effectivement, sinon c'est ridicule :)

Citation:

---

Envoyé par cestpasmoinonplus

Mais pour l'heure, je ne veux surtout pas supprimer mon fichier bak. J'aimerais juste supprimer les différentielles les plus anciennes contenues dans ce fichier bak, pour pouvoir continuer à faire des différentielles dans ce fichier sans qu'il grossisse démesurément.

---

Houlà, j'avais pas compris la demande comme ça moi.

La première chose à arrêter de faire c'est justement de mettre plusieurs jeux de sauvegarde dans le même fichier de sauvegarde !
Faut oublier le "backup device".
C'est une vrai mauvaise idée de la part de Microsoft d'avoir proposé ça.
Imagine le temps qu'il faut pour ouvrir un tel fichier. :(


La façon "plan de maintenance" est de créer un dossier de sauvegarde par base et d'y ajouter autant de fichiers qu'on fait de sauvegarde (complète, différentielle, journaux, voire groupe de fichier ou fichier).
Le nom du fichier embarquant le nom de la base, un timestamp et le type de la sauvegarde (.bak, .trn)
Perso un fichier de sauvegarde doit porter l'extension .bak; J'ai donc des sauvegardes .full.bak, .diff.bak et .log.bak

Du coup c'est large plus facile de gérer les suppressions !

Citation:

---

Envoyé par Michel.Priori

Perso un fichier de sauvegarde doit porter l'extension .bak; J'ai donc des sauvegardes .full.bak, .diff.bak et .log.bak

---

Ah ben c'est ça que je cherchais, je ne savais pas :? qu'on pouvait faire ça !

Citation:

---

Envoyé par Michel.Priori

Du coup c'est largement plus facile de gérer les suppressions !

---

Tu m'étonnes :D

Merci Michel :ave:.

Plusieurs choses :

1/ On peut mettre plusieurs sauvegardes dans le même fichier avec SQL Server
Comme l'a dit Michel.Priori, c'est une mauvaise idée, car le fichier va vite devenir gigantesque.
C'est un résidu de la glorieuse époque des sauvegardes sur bande : on ne peut que rajouter à la fin. Pas de suppression, pas de réécriture (ou alors on efface toute la bande avant).
=> Donc pas moyen de faire de "nettoyage" dans ce genre de fichier. Les sauvegardes s'accumulent au fil du temps, et c'est une très mauvaise idée, pour tout un tas de raisons.

2/ On peut faire des backups différentiels avec SQL Server
Alors oui mais pas complètement... La logique voudrait que si on fait deux différentiels de suite, le second ne contienne que le delta par rapport au premier. Mais c'est faux. Il conserve le delta par rapport à la dernière sauvegarde complète.
On se retrouve donc avec soit des FULL en surnombre afin de conserver des DIFF de taille raisonnable, soit des DIFF qui prennent au final plus de place que le FULL.
De mon point de vue la sauvegarde différentielle est une fausse bonne idée.

3/ On peut sauvegarder le journal des transactions avec SQL Server
Et c'est là que la magie de SQL Server opère.
On passe la base de données en mode recovery full, afin que TOUTES les transactions, COMMITEES OU NON restent dans le journal des transactions.
A première vue, les journaux vont grossir de manière alarmante... Sauf qu'on peut les sauvegarder, et qu'une fois sauvegardés, toutes les transactions COMMITEES sont supprimées du fichier.
Ainsi, on fait un FULL de temps en temps (une fois par jour ou une fois par semaine selon l'activité de la base), et on fait des LOG toutes les quelques minutes/heures.
Ainsi, on obtient un REEL différentiel. Chaque sauvegarde de LOG ne contient que les transactions commitées depuis la dernière sauvegarde (FULL ou LOG).
Autre avantage, les sauvegardes des LOG ne sont pas des sauvegardes des données, mais des sauvegardes des transactions. Ainsi, il est possible, à la restauration, de rejouer une à une les transactions et retrouver la base de données dans un état précis.
Et même mieux : si une personne vient de tout péter, et que la base n'a pas été sauvegardée depuis plusieurs heures, il suffit de faire une sauvegarde de journal, puis le restaurer... jusqu'à l'instant avant que la personne fasse une connerie.

Ainsi, FULL + LOG permet de récupérer la base de données avec une granularité de la transaction, à tout moment, y compris APRES la dernière sauvegarde (bon, sauf si le serveur est crashé et que le log courant est illisible, à ce moment on ne peut pas faire des miracles non plus).

Rappel : si la licence le permet ne pas hésiter à utiliser l'option compress lors de la sauvegarde (si pas possible voir si le backup dans un "dossier compressé" (.zip) est jouable en terme de temps de restauration)

Pour revenir sur l'utilité de la sauvegarde différentielle :
Il est vrai que la bonne gestion de la planification de celle-ci n'est pas facile.
Soit une base en mode complet - backup le dimanche 14h15 - durée 30 minutes
Planification du backup du journal toutes les heures - durée quelques minutes selon l'activité

--> besoin de restauration le jeudi => Full + toute l'activité transactionnelle du dimanche au jeudi ; ça peut être long voire très long :(

En imaginant que l'activité transactionnelle est essentiellement du au DML et que l'accroissement de la base est de 1% par jour.
Le backup différentiel du lundi va, grosso modo, occuper 6% de la base (2% insert, 2% update, 1% delete)
Le backup du mardi, ne pouvant être plus faible que celui du lundi, va occuper 11% (et oui, les update vont certainement modifier les lignes déjà updatées ou inserées)
Le mercredi c'est encore moins prévisible et ainsi de suite.
Dès qu'on atteint 50% de la base initiale il est temps de faire une complète.

La seule chose importante à mes yeux est la rapidité du restaure.

Si on reprend le même besoin de restauration pour le jeudi => (Full + Diff du mercredi) + Log du mercredi à jeudi
C'est quand même plus rapide de ne rejouer l’activé transactionnelle QUE sur 1 jour

A ne pas laisser définitivement tomber.

Pour ma part, j'utilise actuellement la politique suivante sur les serveurs que j'administre (bases jusqu'à 40 Go, donc pas énormes).

1 full toutes les 24h
1 log toutes les 10 voir 5 minutes

Jusqu'à présent, j'ai principalement du restaurer des backup pour raison de clone (tests de mises à jour, etc.), rarement pour des problèmes de données corrompues.

Le pire que j'ai eu, c'est un backup FULL qui n'a pas marché, et évidement, erreur utilisateur le lendemain après-midi.
J'ai donc du restaurer une base de 38 Go + environ 36 heures de log (à raison de 1 log toutes les 5 minutes).
La restauration a duré en tout et pour tout 15 minutes.

Le temps de s'entendre avec le client sur la restauration ou non, et l'heure à restaurer : plus de 3 heures.

Avec un tel ratio temps "machine" vs temps "humain", je trouve cette politique satisfaisante.

Pour ce qui est du FULL, il tourne chaque jour avec une rotation de quelques minutes (suite à un bug dans mon programme de backup, mais au final c'est pas mal, toutes les bases ne font pas un full en même temps du coup). Côté utilisateur, je n'ai jamais eu le moindre retour comme quoi la base était plus lente durant le FULL : SQL Server gère très bien ça. Actuellement le FULL dure moins de 10 minutes, y compris quand il tourne en pleine journée (ou pire, en pleine nuit, pendant des traitements intensifs en base). A nouveau, ce temps de sauvegarde est je trouve satisfaisant.

En revanche, je ne compresse pas les backups : je pense qu'au prix du To actuel, c'est bouffer du CPU pour rien (précieux sur un SQL Server !)
En revanche, pour plus de sécurité, j'effectue les backups sur un chemin réseau, donc je fais un contrôle checksum à la fin.

Sinon, Express ne permet effectivement pas de faire de la compression. En revanche, on peut restaurer un backup compressé dessus.

Compression de la sauvegarde :

1- L'objectif de ce post est en droite ligne avec cette option. Ok.
2- Est-ce utile, même si on a de la place ?
Pour ce qui m'a été donné de voir ma réponse est oui.
Le ratio CPU vs taux de compression est tel que la performance globale, même au restore, en est amélioré : moins de lectures disques => plus de rapidité.

Si on veux chercher les contre arguments
* il faut voir le ratio effectif de compression, qui lui même est dépendant du type de contenu (blob, filestream, ...)
* il faut voir si le PRA reste compatible : plus de CPU en masse pour diverses opérations, les délais risquent de s'allonger (les accès disques aussi ! du coup moins de lecture est plutôt une bonne chose. Il n'y a que le test grandeur nature qui peut trancher)

Les faux arguments :
* cela 'brouille' la sauvegarde : Ah, et en dehors d'un restore on sait lire, en clair, le fichier de backup ?
* le fichier est plus fragile : c'est quoi un fichier fragile ? c'est le support qui est en cause. En réalité un fichier plus petit est statistiquement moins sensible à une défaillance du support.

C'est une techno qui est officiellement disponible depuis plus de 10 ans maintenant, on peut lui accorder le status de 'stable' ;)

Citation:

---

Envoyé par StringBuilder

bon, sauf si le serveur est crashé et que le log courant est illisible

---

De ce que je crois savoir, le backup de la base n'est plus possible dans cette situation.
C'est stupide car les fichiers de données sont peut-être encore sains et les blocs dirty sont encore en mémoire.

Je suis preneur de la manip à faire si le fichier de log courant est défaillant mais l'instance est toujours UP.

Pour la compression, dans mon environnement, les CPU sont très "light", alors que l'unité de sauvegarde sur laquelle je sauvegarde est en RAID 10 de SSD avec des vitesses autour de 400 Mo/s. Du coup la compression ralenti tout (le temps de sauvegarde et le serveur en général).
Après, sur des disques magnétiques plus lents, l'économie d'IO liées à la compression peut effectivement être intéressante.

Pour ce qui est de récupérer une base de données suite à un crash serveur avec log illisible, pour moi le plus simple c'est de monter le MDF en demandant à reconstruire un nouveau journal :
https://www.sqlserverlogexplorer.com...hout-ldf-file/

Après, pas certain que le MDF soit attachable vu qu'il n'aura pas été démonté proprement.
Dans ce cas, rien d'autre à faire que de repartir des backups

Citation:

---

Envoyé par Michel.Priori

De ce que je crois savoir, le backup de la base n'est plus possible dans cette situation.
C'est stupide car les fichiers de données sont peut-être encore sains et les blocs dirty sont encore en mémoire.

Je suis preneur de la manip à faire si le fichier de log courant est défaillant mais l'instance est toujours UP.

---

Une simple copie des fichiers avec un

Code:

---

CREATE DATABASE … (…) FOR ATTACH_REBUILD_LOG

---

Pour info, le seul cas ou la compression est inefficace et même négative est le cas ou la base est chiffrées avec TDE...

A +

Citation:

---

Envoyé par StringBuilder

Plusieurs choses :
De mon point de vue la sauvegarde différentielle est une fausse bonne idée.

---

C'est une très bonne idée pour nous, en tout cas, car nous avons besoin de minimiser au maximum le temps d'exécution de la sauvegarde.
Après, il suffit de choisir le bon moment d'exécuter une full. Dans notre cas, une fois par semaine, avec des diff toutes les heures, en ne gardant que les cinq dernières diff, ça devrait le faire.

Citation:

---

Envoyé par Michel.Priori

Rappel : si la licence le permet ne pas hésiter à utiliser l'option compress lors de la sauvegarde

---

Tout à fait. :P

Citation:

---

Envoyé par Michel.Priori

La seule chose importante à mes yeux est la rapidité du restaure.

---

Mais pas pour nous, c'est l'inverse. Rapidité max pour la sauvegarde, et chaise longue pour l'éventuel restaur. ;)

Citation:

---

Envoyé par Michel.Priori

Compression de la sauvegarde :
2- Est-ce utile, même si on a de la place ?
Pour ce qui m'a été donné de voir ma réponse est oui.
Le ratio CPU vs taux de compression est tel que la performance globale, même au restore, en est amélioré : moins de lectures disques => plus de rapidité.

---

Ben ouai, j'ai été surpris de le constater. Le backup avec compression est plus rapide que sans compression. Ben super :P

Citation:

---

Envoyé par SQLpro

Une simple copie des fichiers avec un

Code:

---

CREATE DATABASE … (…) FOR ATTACH_REBUILD_LOG

---

---

:weird:
On est bien d'accord sur le scénario :

Citation:

---

le fichier de log courant est défaillant mais l'instance est toujours UP.

---

Je n'ai pas fait le test, mais il me semble que :
* la base est inaccessible
* Les fichiers de données sont encore ouverts
* il y a encore des blocs mémoire correspondant à cette base et potentiellement dirty en cache

Du coup :
1- je ne pense pas (pas fait le test) que la copie des fichiers ouverts est possible
2- la doc indique :

Citation:

---

FOR ATTACH_REBUILD_LOG requiert ce qui suit : •Un arrêt propre de la base de données.

---

https://docs.microsoft.com/fr-fr/sql...ql-server-2017
Ce qui n'est pas le cas !

As-tu un scénario de test qui met ça en œuvre ?