Est-il possible de restaurer une DB si l'un des backups intemédiaire est perdu?
Cette question n'était pas anodine, c'est un cas vécu par un client.
Comme elle a piqué mon intérêt, j'y ai consacré un peu de temps pour y fournir une réponse précise.
Réponse
Oui, si le backup mode est "full" et que l'on dispose de tous les backups de transaction log intermédiaires.
Comme quoi il peut être utile de ne pas détruire ses backups trop vite!
Le cas de figure
Une DB SqlServeur en backup mode "Full" est backupée tous les jours (un fichier par jour) et le transaction log est backupé plusieurs fois par jours (également un fichier par jour).
Dans le temps, nous obtenons donc la logique suivante:
- T1: backup DB 1
- T2: backup Log 1
- T3: backup DB 2 Le backup perdu.
- T4: backup log 2
- T5: Crash... et il faut restaurer la DB.
Le cas idéal est bien évidement de restaurer le dernier backup connu (Backup DB 2 fait à T3) et d'appliquer le transaction log backup (Backup log 2 fait à T4).
Mais dans le cas qui nous concerne, "backup DB 2" a été corrompu (erreur disque).
Alternative de restauration:
Par change le "backup log 2" n'a pas été corrompu et comme "Backup DB 1" et "Backup log 1" sont à notre disposition, il est quand même possible de restorer la DB dans l'état du temps T4.
L'opération à conduire est la suivante:
- Restorer le backup DB 1
- Restorer le backup Log 1
- Restorer le backup Log 2
En backup mode "full", seul les parties du transaction log backupée avec "Backup transaction" sont libérées.
Ces parties peuvent dont être réutilisées par Sql Serveur pour y enregistrer de nouvelles transaction.
En lisant entre les lignes, cela veut dire que s'il n'y a jamais de BACKUP TRANSACTION pour une DB configurée en backup mode "full", le transaction log va gonfler... gonfler... jusqu'a consommer tous l'espace de disponible sur le disque.
Lorsque l'on fait un backup de la DB, il y a normalement une opération de CHECKPOINT. Cette dernière reporte les modifications enregistrées dans le Log File sur le Data file.
Ensuite le data file est backupé.
En aucun cas, cette opération de CHECKPOINT (et backup DB) n'altère le log file (hormis l'inscription de l'opération de checkpoint ou de backup).
En conséquence, il n'y a pas de libération d'espace dans le transaction log.
C'est la prochaine opération "Backup Transaction" qui libère la partie devenue inactive du transaction log ("inactive" parce que backupée).
Donc, selon le cas de figure énoncé plus haut, l'opération "Backup log 2" a backupé toutes les transactions entre les temps T1 et T4 (même si une partie d'entre elles ont déjà été appliqués dans le Data file par des checkpoints).
Exemple à l'appui
Voici un petit exemple qui crée des entrées dans une table en insérant ci et là des operations de backups (tantot DB, tantot log) dans des fichiers différents.
Soit les opérations suivantes:
- Insertion des records 1 à 100.000
- Backup DB 1
- Insertion des records 100.000 à 150.000
- Backup Log 1
- Insertion des records 150.001 à 200.000
- Backup DB 2 Le backup supposé perdu.
- Insertion des records 200.001 à 250.000
- Backup Log 2
L'exemple constitué des scripts ci-dessous démontre qu'il est possible de récupérer la DB dans l'état final sans utiliser le backup intermédiaire "Backup DB 2".
Création de la DB de test
Attention, le backup mode doit être "full"USE [master] GO CREATE Database PlayGround GO ALTER DATABASE [PlayGround] SET RECOVERY FULL WITH NO_WAIT GO use [PlayGround] GO Create Table dbo.ListInt ( value int not null ) GO Alter table dbo.ListInt Add Constraint PK_ListInt PRIMARY KEY ( value ) GO -- Populate the test table with records. -- if parameters are missing, just a a new record at the end of the table -- if @StartValue = null select last value +1 -- if @EndValue = null select evaluated StartValue + 1CREATE PROCEDURE dbo.PopulateList @StartValue int, @EndValue int AS DECLARE @Position int if @StartValue is null SELECT @Position = Coalesce( MAX( value )+1, 1) from dbo.ListInt else SET @Position = @StartValue DECLARE @EndPosition int if @EndValue is null SET @EndPosition = @Position + 1 -- will insert only one row else SET @EndPosition = @EndValue -- Insert rows while( @Position <= @EndPosition ) BEGIN insert dbo.ListInt ( value ) values ( @Position ) Set @Position = @Position + 1 END GO
Remplissage et backup
Use PlayGround GO -- prepare initial data (0-100.000) + backupexec dbo.PopulateList @StartValue = 1, @EndValue = 100000 GO BACKUP DATABASE PLAYGROUND to disk = 'c:\Backup1.bak' GO -- Add data (100.000-150.000) + Backup Log exec dbo.PopulateList @StartValue = 100001, @EndValue = 150000 GO BACKUP LOG PLAYGROUND to disk = 'c:\Backup1.log' GO -- Add Data (150.001-200.000) + BACKUP !!!! exec dbo.PopulateList @StartValue = 150001, @EndValue = 200000 GO BACKUP DATABASE PLAYGROUND to disk = 'c:\Backup2.bak' GO -- Add Data (200.001 - 250.000) + BACKUP LOG exec dbo.PopulateList @StartValue = 200001, @EndValue = 250000 GO BACKUP LOG PLAYGROUND to disk = 'c:\Backup2.log' GO -- Question: -- Would it be possible to restore the database from -- Backup1.Bak + Backup1.log + Backup2.Log -- (because we lost Backup2.bak)
Restoration de la DB
Après avoir effacé la DB, le script suivant démontre qu'il est possible de la restaurer sans utiliser le "backup 2" de la base de donnée.
-- Some interesting read from the NET -- SQL SERVER – Restore Database Backup using SQL Script (T-SQL) -- http://blog.sqlauthority.com/2007/02/25/sql-server-restore-database-backup-using-sql-script-t-sql/ -- -- How to restore a SQL Server transaction log backup -- Also explain how to recover multiple LOGs backuped to a file -- http://www.mssqltips.com/tutorial.asp?tutorial=111 --USE master GO -- Retreive Logical Filenames from data file RESTORE FILELISTONLY FROM DISK = 'c:\Backup1.bak' GO -- Restore Initial backup (NORECOVERY) RESTORE DATABASE PlayGround FROM DISK = 'c:\Backup1.bak' WITH MOVE 'PlayGround' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PlayGround.mdf', MOVE 'PlayGround_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PlayGround_log.LDF', NORECOVERY GO -- Restore the First Transaction Log RESTORE LOG PlayGround FROM disk = 'c:\Backup1.log' WITH NORECOVERY GO -- Restore the SECOND Transaction Log -- (we lost intermediate backup Backup1.Bak) RESTORE LOG PlayGround FROM disk = 'c:\Backup2.log' WITH NORECOVERY GO -- Recover the Database RESTORE DATABASE PlayGround WITH RECOVERY GO -- Set Multi-User ALTER DATABASE PlayGround SET MULTI_USER GO -- Count number of records (250.000 expected) use playGround GO select COUNT(*) from ListInt GO
Références
- SQL SERVER – Restore Database Backup using SQL Script (T-SQL)
- How to restore a SQL Server transaction log backup ++++
Explique également comment restaurer les multiples Bakups Log stockés dans une seul fichier de backup.