Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Dec 21 '13 at 5:09

This question came from our site for professional and enthusiast programmers.

2 Answers 2

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 :

enter image description here

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 :

enter image description here

--- 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.

enter image description here

share|improve this answer
    
Just another similar example to reference: blogs.msdn.com/b/sqlserverfaq/archive/2010/08/26/… –  Shawn Melton Dec 22 '13 at 6:11
    
@ShawnMelton Thanks, the idea is same but that has explained that a full or diff backup wont break a T-log chain. –  Kin Dec 22 '13 at 18:23

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

share|improve this answer
    
there were no transactions submitted at this time, so wouldnt the trans log just be a blank transaction log? –  Scott H Dec 20 '13 at 8:59
1  
If no transactions were submitted is should be possible since there should be no missing transaction sequence numbers. You can use ´RESTORE HEADERONLY´ to check the transaction log sequence numbers in the log files you have to verify that no sequence numbers are missing. See sqlbackuprestore.com/logsequencenumbers.htm for more info. –  Daniel B Dec 20 '13 at 9:56
    
how would i go about restoring the missing transaction log? could i just modify the last lsn of the previous trans log in msdb to match the first lsn of the new transaction log? –  Scott H Dec 20 '13 at 10:36
    
No. Based on The activity that was present in the database during the period of time encompassed by the missing log backup, the system will either let you go past it or it won't. If it doesn't, that means that there was actually activity in the db and you can't skip it and hope to maintain a consistent database. –  Ben Thul Dec 20 '13 at 10:54
    
@DanielB Your answer is not accurate as it can be done if there is NO Gap in the log sequence number between the log backups. Refer to my answer for more details. –  Kin Dec 22 '13 at 3:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.