SQL Server – How to restore database with old full backup and transaction log

It is possible to recover database with any full database and all subsequent transaction log if LOG chain is intact.

SQL Server Transaction log chain

A continous sequence of log bcakup is called a log chain. Whenever you create database backup for first time
its generate log chain or if you change the recovery model of database from simple to full or
bulk logged recovery.

If you choose to overwrite the existing backup that will break the log chain.

If log chain is not broken you can restore database with any full backup and all subsequent log backups up to point your recovery point.

To understand, how to restore full backup and subsequent transaction log. We are going to create demo database with below example.

Steps to restore database with old backup

create database demodb

— Create temp table in demodb database
use demodb

create table tblstudent
(
rollno int,
name varchar(20)
)
— After creating table, we are taking full backup that will start the Log chain (LSN number).
backup database demo_db
to disk = ‘D:\SQL2008\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\demo_db_full.bak’

— Here We are adding 3 records in the table

insert into tblstudent
values(1001,’Sandeep’)

insert into tblstudent
values(1002,’Sonu’)

insert into tblstudent
values(1003,’Mahesh’)

— Now take a log backup

backup log demo_db
to disk = ‘D:\SQL2008\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\demo_db_1.log’

— We are dropping rollno 1001
delete from tblstudent where rollno = 1001

— Take full backup again

backup database demo_db
to disk = ‘D:\SQL2008\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\demo_db_full2.bak’

insert into tblstudent
values(1007,’Sunita’)

— Take log backup to maintain the Log chain

backup log demo_db
to disk = ‘D:\SQL2008\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\demo_db_2.log’

— restore database with old full backup
use master
restore database demo_db
from disk = ‘D:\SQL2008\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\demo_db.bak’
with replace, norecovery

— Restoring all subsequent transaction logs to restore database.
restore log demo_db from disk = ‘D:\SQL2008\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\demo_db_1.log’ with norecovery
restore log demo_db from disk = ‘D:\SQL2008\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\demo_db_2.log’ with recovery

Leave a Reply

  

  

  


− 2 = 7