If you lost some intermediate backup files?

  • Article

It’s sometimes really challengeable to restore a database if you lost some intermediate backup files such as Full or Differential backups.

Let's assume backups have been taken in below order.

Timeline Backup Type Backup Name
t1 Full Backup F1
t2 Diff Backup D1
t3 TLog Backup T1
t4 TLog Backup T2
t5 Full Backup F2
t6 Diff Backup D2
t7 TLog Backup T3
t8 TLog Backup T4
t9 Diff Backup D3
t10 TLog Backup T5
t11 TLog Backup T6

 

If some of these backup files (exp. F2,D1,D2 or D3) are lost or damaged, don’t worry you still have a chance to restore the database to "t11" time.

Different restore paths can be used to restore underlying database to "t11" time. You can see the valid restore paths below

image

But the below restore paths are invalid

F1-D3-T5-T6 (Because D3 differential backup needs F2 full backup)
F2-D3-T6 (Because T6 log backup needs T5 log backup)

Here some additional notes for Differential and TLog backups;

  • For Differential backups;
            Only the last differential backup needs to be restored
            Differential backup needs last taken full backup.
            Only full backup affect the differential backup sequence
  • For TLog backups;
            All log backups need to be restored
            Log backup needs previous log backups. They are incremental.
            Only log backups affect the log backup sequence

And now let’s make a demo to see above valid restore paths

/*

Restore Paths Demo

Turgay Sahtiyan

Microsoft Senior SQL PFE

www.turgaysahtiyan.com

*/

/*

Backups have been taken in this order

Timeline Backup Type Backup Name

-------- ----------- -----------

t1                Full Backup F1

t2                Diff Backup D1

t3                TLog Backup T1

t4                TLog Backup T2

t5                Full Backup F2

t6                Diff Backup D2

t7                TLog Backup T3

t8                TLog Backup T4

t9                Diff Backup D3

t10               TLog Backup T5

t11               TLog Backup T6

Valid Restore Paths

------------------------

Restore Path 1 : F2-D3-T5-T6

Restore Path 2 : F2-D2-T3-T4-T5-T6

Restore Path 3 : F2-T3-T4-T5-T6

Restore Path 4 : F1-D1-T1-T2-T3-T4-T5-T6

Restore Path 5 : F1-T1-T2-T3-T4-T5-T6

Some Invalid Restore Paths

-------------------------

F1-D3-T5-T6 (Because D3 differential backup needs F2 full backup)

F2-D3-T6 (Because T6 log backup needs T5 log backup)

*/

USE [master]

GO

--Create a folder (C:\SQLData) to store the databases

--Create a folder (C:\SQLBackup) to store the backup files

--Create Work Database

CREATE DATABASE db_RestorePath

GO

ALTER DATABASE db_RestorePath SET RECOVERY FULL

GO

USE db_RestorePath

GO

CREATE TABLE tbl_work(TimeLine char(3))

GO

--t1 - Full Backup - F1

INSERT tbl_work VALUES('t1')

GO

BACKUP DATABASE db_RestorePath TO DISK = 'C:\SQLBackup\F1.bak'

WITH INIT, NAME = 'F1'

GO

--t2 - Diff Backup - D1

INSERT tbl_work VALUES('t2')

GO

BACKUP DATABASE db_RestorePath TO DISK = 'C:\SQLBackup\D1.bak'

WITH DIFFERENTIAL, INIT, NAME = 'D1'

GO

--t3 - TLog Backup - T1

INSERT tbl_work VALUES('t3')

GO

BACKUP LOG db_RestorePath TO DISK = 'C:\SQLBackup\T1.trn'

WITH INIT, NAME = 'T1'

GO

--t4 - TLog Backup - T2

INSERT tbl_work VALUES('t4')

GO

BACKUP LOG db_RestorePath TO DISK = 'C:\SQLBackup\T2.trn'

WITH INIT, NAME = 'T2'

GO

--t5 - Full Backup - F2

INSERT tbl_work VALUES('t5')

GO

BACKUP DATABASE db_RestorePath TO DISK = 'C:\SQLBackup\F2.bak'

WITH INIT, NAME = 'F2'

GO

--t6 - Diff Backup - D2

INSERT tbl_work VALUES('t6')

GO

BACKUP DATABASE db_RestorePath TO DISK = 'C:\SQLBackup\D2.bak'

WITH DIFFERENTIAL, INIT, NAME = 'D2'

GO

--t7 - TLog Backup - T3

INSERT tbl_work VALUES('t7')

GO

BACKUP LOG db_RestorePath TO DISK = 'C:\SQLBackup\T3.trn'

WITH INIT, NAME = 'T3'

GO

--t8 - TLog Backup - T4

INSERT tbl_work VALUES('t8')

GO

BACKUP LOG db_RestorePath TO DISK = 'C:\SQLBackup\T4.trn'

WITH INIT, NAME = 'T4'

GO

--t9 - Diff Backup - D3

INSERT tbl_work VALUES('t9')

GO

BACKUP DATABASE db_RestorePath TO DISK = 'C:\SQLBackup\D3.bak'

WITH DIFFERENTIAL, INIT, NAME = 'D3'

GO

--t10 - TLog Backup - T5

INSERT tbl_work VALUES('t10')

GO

BACKUP LOG db_RestorePath TO DISK = 'C:\SQLBackup\T5.trn'

WITH INIT, NAME = 'T5'

GO

--t11 - TLog Backup - T6

INSERT tbl_work VALUES('t11')

GO

BACKUP LOG db_RestorePath TO DISK = 'C:\SQLBackup\T6.trn'

WITH INIT, NAME = 'T6'

GO

-------------------------------

-----Valid Restore Paths-------

-------------------------------

--Restore Path 1

--Shortest Path

--Restore below backups

--F2-D3-T5-T6

USE [master]

RESTORE DATABASE [db_RestorePath1] FROM DISK = N'C:\SQLBackup\F2.bak'

      WITH MOVE N'db_RestorePath' TO N'C:\SQLData\db_RestorePath1.mdf'

            , MOVE N'db_RestorePath_log' TO N'C:\SQLData\db_RestorePath1_log.ldf'

            , NORECOVERY

RESTORE DATABASE [db_RestorePath1] FROM DISK = N'C:\SQLBackup\D3.bak'

      WITH NORECOVERY

RESTORE LOG [db_RestorePath1] FROM DISK = N'C:\SQLBackup\T5.trn'

      WITH NORECOVERY

RESTORE LOG [db_RestorePath1] FROM DISK = N'C:\SQLBackup\T6.trn'

GO

--

USE db_RestorePath1

GO

select * from tbl_work

--Restore Path 2

--If you lost D3 differential backup, you can skip it

--Restore below backups

--F2-D2-T3-T4-T5-T6

USE [master]

RESTORE DATABASE [db_RestorePath2] FROM DISK = N'C:\SQLBackup\F2.bak'

      WITH MOVE N'db_RestorePath' TO N'C:\SQLData\db_RestorePath2.mdf'

            , MOVE N'db_RestorePath_log' TO N'C:\SQLData\db_RestorePath2_log.ldf'

            , NORECOVERY

RESTORE DATABASE [db_RestorePath2] FROM DISK = N'C:\SQLBackup\D2.bak'

      WITH NORECOVERY

RESTORE LOG [db_RestorePath2] FROM DISK = N'C:\SQLBackup\T3.trn'

      WITH NORECOVERY

RESTORE LOG [db_RestorePath2] FROM DISK = N'C:\SQLBackup\T4.trn'

      WITH NORECOVERY

RESTORE LOG [db_RestorePath2] FROM DISK = N'C:\SQLBackup\T5.trn'

      WITH NORECOVERY

RESTORE LOG [db_RestorePath2] FROM DISK = N'C:\SQLBackup\T6.trn'

GO

--

USE db_RestorePath2

GO

select * from tbl_work

--Restore Path 3

--if you lost both D2 and D3 differential backups

--Restore below backups

--F2-T3-T4-T5-T6

USE [master]

RESTORE DATABASE [db_RestorePath3] FROM DISK = N'C:\SQLBackup\F2.bak'

      WITH MOVE N'db_RestorePath' TO N'C:\SQLData\db_RestorePath3.mdf'

            , MOVE N'db_RestorePath_log' TO N'C:\SQLData\db_RestorePath3_log.ldf'

            , NORECOVERY

RESTORE LOG [db_RestorePath3] FROM DISK = N'C:\SQLBackup\T3.trn'

      WITH NORECOVERY

RESTORE LOG [db_RestorePath3] FROM DISK = N'C:\SQLBackup\T4.trn'

      WITH NORECOVERY

RESTORE LOG [db_RestorePath3] FROM DISK = N'C:\SQLBackup\T5.trn'

      WITH NORECOVERY

RESTORE LOG [db_RestorePath3] FROM DISK = N'C:\SQLBackup\T6.trn'

GO

--

USE db_RestorePath3

GO

select * from tbl_work

--Restore Path 4

--if you lost F2 Full backup

--Restore below backups

--F1-D1-T1-T2-T3-T4-T5-T6

USE [master]

RESTORE DATABASE [db_RestorePath4] FROM DISK = N'C:\SQLBackup\F1.bak'

      WITH MOVE N'db_RestorePath' TO N'C:\SQLData\db_RestorePath4.mdf'

            , MOVE N'db_RestorePath_log' TO N'C:\SQLData\db_RestorePath4_log.ldf'

            , NORECOVERY

RESTORE DATABASE [db_RestorePath4] FROM DISK = N'C:\SQLBackup\D1.bak'

      WITH NORECOVERY

RESTORE LOG [db_RestorePath4] FROM DISK = N'C:\SQLBackup\T1.trn'

      WITH NORECOVERY

RESTORE LOG [db_RestorePath4] FROM DISK = N'C:\SQLBackup\T2.trn'

      WITH NORECOVERY

RESTORE LOG [db_RestorePath4] FROM DISK = N'C:\SQLBackup\T3.trn'

      WITH NORECOVERY

RESTORE LOG [db_RestorePath4] FROM DISK = N'C:\SQLBackup\T4.trn'

      WITH NORECOVERY

RESTORE LOG [db_RestorePath4] FROM DISK = N'C:\SQLBackup\T5.trn'

      WITH NORECOVERY

RESTORE LOG [db_RestorePath4] FROM DISK = N'C:\SQLBackup\T6.trn'

GO

--

USE db_RestorePath4

GO

select * from tbl_work

--Restore Path 5

--if you lost both F2 Full backup and D1 Differential backup

--Restore below backups

--F1-T1-T2-T3-T4-T5-T6

USE [master]

RESTORE DATABASE [db_RestorePath5] FROM DISK = N'C:\SQLBackup\F1.bak'

      WITH MOVE N'db_RestorePath' TO N'C:\SQLData\db_RestorePath5.mdf'

            , MOVE N'db_RestorePath_log' TO N'C:\SQLData\db_RestorePath5_log.ldf'

            , NORECOVERY

RESTORE LOG [db_RestorePath5] FROM DISK = N'C:\SQLBackup\T1.trn'

      WITH NORECOVERY

RESTORE LOG [db_RestorePath5] FROM DISK = N'C:\SQLBackup\T2.trn'

      WITH NORECOVERY

RESTORE LOG [db_RestorePath5] FROM DISK = N'C:\SQLBackup\T3.trn'

      WITH NORECOVERY

RESTORE LOG [db_RestorePath5] FROM DISK = N'C:\SQLBackup\T4.trn'

      WITH NORECOVERY

RESTORE LOG [db_RestorePath5] FROM DISK = N'C:\SQLBackup\T5.trn'

      WITH NORECOVERY

RESTORE LOG [db_RestorePath5] FROM DISK = N'C:\SQLBackup\T6.trn'

GO

--

USE db_RestorePath5

GO

select * from tbl_work

-------------------------------

-----Invalid Restore Paths-------

-------------------------------

--F1-D3-T5-T6

--D3 differential backup needs F2 full backup

USE [master]

RESTORE DATABASE [db_RestorePath6] FROM DISK = N'C:\SQLBackup\F1.bak'

      WITH MOVE N'db_RestorePath' TO N'C:\SQLData\db_RestorePath6.mdf'

            , MOVE N'db_RestorePath_log' TO N'C:\SQLData\db_RestorePath6_log.ldf'

            , NORECOVERY

RESTORE DATABASE [db_RestorePath6] FROM DISK = N'C:\SQLBackup\D3.bak'

      WITH NORECOVERY

RESTORE LOG [db_RestorePath6] FROM DISK = N'C:\SQLBackup\T5.trn'

      WITH NORECOVERY

RESTORE LOG [db_RestorePath6] FROM DISK = N'C:\SQLBackup\T6.trn'

GO

--Analyze the error message

--This differential backup cannot be restored because the database

--has not been restored to the correct earlier state.

--F2-D3-T6

--T6 log backup needs T5 log backup

USE [master]

RESTORE DATABASE [db_RestorePath7] FROM DISK = N'C:\SQLBackup\F2.bak'

      WITH MOVE N'db_RestorePath' TO N'C:\SQLData\db_RestorePath7.mdf'

            , MOVE N'db_RestorePath_log' TO N'C:\SQLData\db_RestorePath7_log.ldf'

            , NORECOVERY

RESTORE DATABASE [db_RestorePath7] FROM DISK = N'C:\SQLBackup\D3.bak'

      WITH NORECOVERY

RESTORE LOG [db_RestorePath7] FROM DISK = N'C:\SQLBackup\T6.trn'

GO

--Analyze the error message

--The log in this backup set begins at LSN 31000000025100001, which is

--too recent to apply to the database. An earlier log backup that includes

--LSN 31000000025000001 can be restored.

----Drop Work Databases

--DROP DATABASE db_RestorePath

--DROP DATABASE db_RestorePath1

--DROP DATABASE db_RestorePath2

--DROP DATABASE db_RestorePath3

--DROP DATABASE db_RestorePath4

--DROP DATABASE db_RestorePath5

--DROP DATABASE db_RestorePath6

--DROP DATABASE db_RestorePath7