Restaurer une DB en ayant perdu un backup intermédiaire

Voici une question soulevée il y a quelque-jours:

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.

Cas de restauration idéal:
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:

1. Restorer le backup DB 1
2. Restorer le backup Log 1
3. Restorer le backup Log 2

Pourquoi est-ce que cela marche?
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

Simulation des différentes opérations de backups

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.