Missing Transaction log in chain, Possible to skip

Question

We have had one of our clients SQL Servers crash and need to restore it to the most recent possible time. We have a full backup from Monday and transaction logs that run every hour up until this morning at 02:01, however we are missing one transaction log from a database so we are only able to restore this database to Wednesday.

Is it possible to skip a transaction log and continue recovering the database to the most recent date with the remaining logs?

EDIT: there were no changes to the database done at the time we are missing, so the transaction log would have nothing in it

Answer 1

Is it possible to skip a transaction log and continue recovering the database to the most recent date with the remaining logs? EDIT: there were no changes to the database done at the time we are missing, so the transaction log would have nothing in it

Yes, only if there is NO gap in the Log Sequence Numbers of the log backups.

Below will explain you in more detail.

create database logbackup_test
go

-- take a full backup, else the database will be in pseudo-full recovery
backup database logbackup_test to disk = 'C:\sandbox\logbackup_test_full.bak'
with compression, stats =10

use logbackup_test
go
CREATE TABLE dbo.Table_1
       (
       Name varchar(50) NULL
       )  ON [PRIMARY]
GO

--Insert a 1st Row in TEST table
Insert into dbo.Table_1 values ('Kin-1')
GO
--Take 1st T-LOG backup.
BACKUP LOG logbackup_test TO  DISK = N'C:\sandbox\logbackup_test-TLog1.trn' with stats =10
go

--Insert a 2nd Row in TEST table
Insert into dbo.Table_1 values ('Kin-2')
GO

--Take 2nd T-LOG backup.
BACKUP LOG logbackup_test TO  DISK = N'C:\sandbox\logbackup_test-TLog2.trn' with stats =10
go

--Insert a 3rd Row in TEST table
Insert into dbo.Table_1 values ('Kin-3')
GO

--Take 3rd T-LOG backup
BACKUP LOG logbackup_test TO  DISK = N'C:\sandbox\logbackup_test-TLog3.trn' with stats =10
go

--

--Take 4th T-LOG backup -- NOTE That there is no activity .. no transactions (NO INSERTS DONE) !!!
BACKUP LOG logbackup_test TO  DISK = N'C:\sandbox\logbackup_test-TLog4.trn' with stats =10
go
/*Processed 0 pages for database 'logbackup_test', file 'logbackup_test_log' on file 1.
100 percent processed.
BACKUP LOG successfully processed 0 pages in 0.072 seconds (0.000 MB/sec).
*/

--

--Insert a 4th Row in TEST table
Insert into dbo.Table_1 values ('Kin-4')
GO
--Take 5th T-LOG backup
BACKUP LOG logbackup_test TO  DISK = N'C:\sandbox\logbackup_test-TLog5.trn' with stats =10
go

Check the data now :

Below will explain you why you can skip the log backup. It relies on LSN (Log Sequence Number). It is beyond the scope of this answer to go into detail about the LSN, but the link will give you a good idea of what it is.

RESTORE HEADERONLY will tell you the first LSN and Last LSN as below :

--- now start the restore

-- restore a full backup
restore database logbackup_test
from disk = N'C:\sandbox\logbackup_test_full.bak'
with norecovery, replace, stats =10
go

-- restore consequent log backups !! - 1,2,3 and 5. WE ARE SKIPPING TLog4 !!
restore log logbackup_test
from disk = N'C:\sandbox\logbackup_test-TLog1.trn'
with norecovery, stats =10
go
restore log logbackup_test
from disk = N'C:\sandbox\logbackup_test-TLog2.trn'
with norecovery, stats =10
go
restore log logbackup_test
from disk = N'C:\sandbox\logbackup_test-TLog3.trn'
with norecovery, stats =10
go
-- NOTE -- I am skipping Log4 as it did not have any transaction .....
restore log logbackup_test
from disk = N'C:\sandbox\logbackup_test-TLog5.trn'
with recovery, stats =10 

Below is the result .. we are able to restore the log backup 1,2,3 and 5. We skipped Tlog4 as the LastLSN of Tlog3 was the firstLSN of Tlog5.

Answer 2

No. Even if it is possible, the resulting data would be meaningless if a transaction log were skipped.