DotMim

A fortiori, on peut dire Oui
Aprés on peut dire aussi “Surtout le dernier, en fait !”

Vraiment le dernier dernier ? Et si je l’ai plus ce dernier là ??

Nous savons tous que le dernier rempart protégeant votre base de données reste la stratégie de sauvegarde que vous avez mis en place.

On peut imaginer une stratégie (pour un mode de récupération complet) basée sur une sauvegarde complète, plus un ou deux différentiel, et enfin les sauvegardes du transaction log.

Je reprends un schéma de MSDN pour illustrer ce cas simple :

Un client m’a posé la question suivante :

“Que se passe t’il si je perds le dernier backup full ? Puis-je tout de même remonter la base jusqu’au dernier log ? '(à supposer qu’au final je n’ai perdu que le dernier backup full mais pas les logs qui ont suivi)”

Bon ok c’est tordu, mais ce qui est aussi intéressant c’est savoir si le backup contiendrait des informations indispensables entre le log qui le précède et le log qui lui succède.

J’ai donc monté un petit script de test, simplissime pour évaluer la situation :

   1: INSERT [dbo].[Employe] ([EmployeId], [Nom], 

   2:     [Prenom], [NumeroCarteCredit]) 

   3: VALUES (N'37cb101c-f2f9-4f28-b9f6-10e4193849ff', 

   4:     N'Pertus', N'Sébastien', N'1234-2344-2333-45555')

   5: Go

   6:  

   7: /*** Premier backup Complet ***/

   8: BACKUP DATABASE [bBackupStrategie] 

   9: TO DISK = N'C:\Projects\Backup\bBackupFull1.bak' 

  10:  

  11:  

  12: Update [dbo].[Employe] 

  13: Set [NumeroCarteCredit] = '1234-2344-2333-888888' 

  14: Where EmployeId= '37cb101c-f2f9-4f28-b9f6-10e4193849ff'

  15: Go

  16: /*** Premier backup Transactionnel ***/

  17: BACKUP LOG [bBackupStrategie] 

  18: TO  DISK = N'C:\Projects\Backup\bBackupTrans1.trn' 

  19:     

  20: GO

  21: Update [dbo].[Employe] 

  22: Set [NumeroCarteCredit] = '1234-2344-2333-777777' 

  23: Where EmployeId= '37cb101c-f2f9-4f28-b9f6-10e4193849ff'

  24: Go

  25: /*** Deuxième backup Transactionnel ***/

  26: BACKUP LOG [bBackupStrategie] 

  27: TO  DISK = N'C:\Projects\Backup\bBackupTrans2.trn' 

  28:  

  29: /*** Deuxième backup Complet ***/

  30: BACKUP DATABASE [bBackupStrategie] 

  31: TO DISK = N'C:\Projects\Backup\bBackupFull2.bak' 

  32:     

  33: Update [dbo].[Employe] 

  34: Set [NumeroCarteCredit] = '1234-2344-2333-666666' 

  35: Where EmployeId= '37cb101c-f2f9-4f28-b9f6-10e4193849ff'

  36: Go

  37: /*** Troisième backup Transactionnel ***/

  38: BACKUP LOG [bBackupStrategie] 

Ok à partir de là, on voit que j’ai 2 backups full et plusieurs transaction Logs

Je vais donc tenter de restaurer ma base de données sans le dernier backup full.

Il me faut donc le premier backup Full ainsi que l’ensemble des transactions logs jusqu’au dernier (sans oublier le tail log que j’inclus dans ma procédure de restauration)

Et voilà ce que ça donne :

   1: USE master;

   2: GO

   3:  

   4: /*** Sauvegarde du tail log ***/

   5: BACKUP LOG [bBackupStrategie] 

   6: TO  DISK = N'C:\Projects\Backup\bBackupTailLog.trn' WITH  NO_TRUNCATE,  NORECOVERY

   7: GO

   8:  

   9: /**** Restauration à partir du 1er Backup complet (et non pas le dernier) ***/

  10: RESTORE DATABASE [bBackupStrategie] 

  11: FROM  DISK = N'C:\Projects\Backup\bBackupFull1.bak'

  12: WITH  FILE = 1,  NORECOVERY

  13: GO

  14: RESTORE LOG [bBackupStrategie] 

  15: FROM  DISK = N'C:\Projects\Backup\bBackupTrans1.trn' 

  16: WITH  FILE = 1,  NORECOVERY

  17: GO

  18: RESTORE LOG [bBackupStrategie] 

  19: FROM  DISK = N'C:\Projects\Backup\bBackupTrans2.trn' 

  20: WITH  FILE = 1,  NORECOVERY

  21: GO

  22: /*** Depart hypothétique si le dernier backup était accessible ***/

  23: --RESTORE DATABASE [bBackupStrategie] 

  24: -- FROM  DISK = N'C:\Projects\Backup\bBackupFull2.bak' 

  25: -- WITH  FILE = 1,  NORECOVERY

  26: --GO

  27:  

  28: RESTORE LOG [bBackupStrategie] 

  29: FROM  DISK = N'C:\Projects\Backup\bBackupTrans3.trn' 

  30: WITH  FILE = 1,  NORECOVERY

  31: GO

  32: RESTORE LOG [bBackupStrategie] 

  33: FROM  DISK = N'C:\Projects\Backup\bBackupTailLog.trn'

  34: WITH  FILE = 1, RECOVERY

  35:  

  36: GO

Et là miracle (ou pas) tout se déroule correctement.

Résultat, le backup complet ne compromets pas la cohérence des données dans le Transaction log.

Bon ce cas de figure est un peu tordu, je vous le concède, mais il a le mérite d’éclaircir un point sous-jacent non négligeable, lui.

Voici le lien vers le source complet de ce script : http://www.dotmim.com/SiteFiles/TipsBackupWithoutLastBackup.sql.txt

Bon backup !

Je vais faire un petit topo sur le plan de maintenance que tout DBA se doit de mettre en place sur ses bases de données. Enfin sur un point particulier du plan de maintenance. Aprés une sauvegarde complète, pourquoi la prochaine sauvegarde du journal de transaction est elle énoôôÔÔôorme…. ?

Avant d’aller plus loin, je me base sur les plans de maintenance, mais il y’a ceux qui préfèrent pour tout se cogner à la main, pour plus de contrôle sur leur plan de maintenance, pour ceux là, je vous conseille de voir par ici : All in one by Hallengren

Après y’a ceux qui passent par les plans de maintenance. Solution intégrée dans SQL SERVER 2005 et 2008, s’appuyant sur des lots SSIS.

Avant d’exposer le problème voici un exemple de plan de maintenance que j’ai rencontré :

Dans ce plan j’ai 3 sous plans :

1. Le premier (copie d’écran précédente) effectue une sauvegarde complète de la base de données, après avoir fait un CheckDB, une reconstruction complète de l’index et une mise à jour des statistiques.
2. Le deuxième sous plan effectue une sauvegarde différentielle, avec une réorganisation de l’index (bon ça c’est pas obligatoire, ça dépend de votre stratégie)
3. Enfin le troisième sous plan effectue une sauvegarde du journal de Log

Au niveau du scheduling, partons sur une théorie qui en vaut une autre : Un backup complet la nuit, un différentiel à Midi et le journal de log toutes les heures.

(Note : Le Update Statistics dans la copie d’écran précédente ne SERT à RIEN : Voir le post de Dieu Paul Randal sur le sujet : http://www.sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-10-Rebuilding-Indexes-and-Updating-Statistics.aspx)

Voici les données du problème : APRES le travail du premier sous plan (sauvegarde complète) la sauvegarde de mon journal de log est énorme (mettons pour l’exemple 100 Mo), alors que ce travail journalier ne représente que peu de transactions et ne génère qu’une petite sauvegarde (mettons 1 Mo)

Voici une copie d’écran de ce qui se passe sur mon poste de dév :

Bon chez moi, avec le peu de données et surtout pas beaucoup de mouvements sur ma base de test, ça représente pas grand chose, mais imaginez ce qui arrive sur une base de données de plusieurs GO ou TB.. ça peut poser très vite un soucis, surtout si celle ci est beaucoup utilisée (ce qui est quand même le principe d’utilisation d’une base !)

Pour exemple, j’ai un client qui travaille sur Team Foundation Server et dont le log sauvegardé après le backup complet fait exactement la taille de la base de données, soit prés de 200 Go…. C’est long et ça prend de la place !

Mais surtout, avant de penser à “comment” résoudre ce problème, c’est “pourquoi” ça grossit comme ça lors de la sauvegarde du premier journal de transaction après la sauvegarde complète ?

Une Fausse idée : Un backup full NE génère PAS d’entrée des données sauvegardées dans le journal de Log (enfin si peu). Bon ça c’est dit, et on l’oublie pas.

Par contre, si, une ré indexation complète EST journalisée, toutes les opérations sont écrites dans le journal de log. Et quand un fait un Rebuild, bah il écrit toutes les données réindexées dans le journal de log (modulo les index non fragmentés non reconstruits) Idem pour la mise à jour des stats par exemple.

Du coup on s’étonne moins de retrouver un log qui correspond presque à la taille de notre base ! (imaginez sur une très très très grosse base) Tout dépend du niveau de fragmentation des index, bref, problématique.

Alors la solution ? En fait, Y’a 2 solutions, tout dépend de votre architecture.

1) Vous disposez de suffisamment de place pour stocker votre log énormissime.

STOP ! Laissez le comme ça le pauvre (enfin pauvre… façon de parler ).

NE FAITES PAS de shrink de votre log. SQL SERVER aura besoin de cet espace au prochain backup complet. Ne faites pas de shrink, vous ne ferez que générer de la fragmentation de votre disque dur, inutilement.

NE FAITES PAS DE SHRINK DANS VOS PLANS DE MAINTENANCE (Vous sentez le cri du coeur là ? Y’a pas UNE société où je ne rencontre pas cette hérésie dans les plans de maintenance ! Prenez des bonnes résolutions pour cette année 2010, virez moi ça de vos plans de maintenance)

Ok votre log ne génère que 10% d’espace en journée, c’est un fait. Mais cet espace est NECESSAIRE à SQL SERVER lors de son plan de maintenance. NE FAITES PAS DE SHRINK (on le dira jamais assez)

2) Vous ne disposez pas de suffisamment de place pour stocker un log énormissime.

Bon, là il va falloir faire des concessions. D’abord cette technique ne fonctionnera pas si vous avez votre base de données en mode Mirroir ou en réplication transactionnelle par exemple (dans ce cas, cf solution 1 et achetez du disque dur )

Si ce n’est pas le cas, on peut créer une solution qui va désactiver le log pendant la reconstruction de l’index complet et le réactiver juste avant le backup (puisque celui ci ne génère pas grand chose dans le log)

SI vous voulez vous convaincre de cette solution, un petit tour sur ce post (de Dieu, encore une fois) devrez vous convaincre de continuer la lecture de ce post :

http://www.sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-the-log-and-log-backups-how-to-convince-yourself.aspx

Bon, l’idée c’est de désactiver l’écriture du journal de log avant la ré indexation et de la réactiver juste après.

Note: je n’utilise pas l’instruction BACKUP LOG MaBase WITH TRUNCATE_ONLY, celle ci est devenue obsolète. Je pointe vers un sujet de Christian sur ce point là : http://blogs.codes-sources.com/christian/archive/2007/08/19/sql-server-interdire-les-op-rations-de-troncation-du-journal-de-transaction-truncate-only-no-log.aspx

Nous utilisons donc une astuce similaire qui consiste à passer du mode de recouvrement Complet au mode de recouvrement Simple (et re hop, re post de Christian sur le sujet : http://blogs.codes-sources.com/christian/archive/2007/02/12/sql-server-faq-sql-pourquoi-mon-fichier-de-log-ldf-est-il-aussi-gros-comment-diminuer-sa-taille.aspx )

Voila ce que ça donne lors de la modification de mon plan de maintenance (Notez au passage que j’ai fais sauter le Update Statistics, qui sert à rien ici)

Etape 1 : Passage en mode simple

Etape 2 : Re passage en mode complet (à placer après la réindexation)

Ce qui donne un plan de maintenance ressemblant à peu prés à ça :

Après exécution de ce plan là, et exécution de la sauvegarde du log, on retrouve quelque chose de plus … concis

Voilà une solution qui permet de sauvegarder de l’espace disque, sans passer par du Shrink File ABUSIF ! (on le dira jamais assez !!!)

Bon Plan de maintenances de Noel à tous !