Point-In-Time Recovery Example in Microsoft SQL Server

Point-In-Time Recovery Example in Microsoft SQL Server

Let's walk through a point-in-time recovery example in Microsoft SQL Server step-by-step. We'll assume for the sake of this example that your database has previously been configured with regular full backups and transaction log backups.

Let's say you have a SQL Server database named "PointInTIME," and you want to perform point-in-time recovery to restore the database to a specific point in time before a data loss occurred.

--Create database

Use master

Create database PointInTIME  

 --create table                                       

Create table test(name varchar(50))                                

 --Insert values then take backup after each inserting values

insert into test values('Full backup backup')  

insert into test values('Mod diff backup backup')

insert into test values('Tue diff backup')

insert into test values('Wed trn backup backup')

insert into test values('Thu trn backup backup')

insert into test values('Fri trn backup backup')

 --Check Values

select * from PointInTIME.dbo.test

 --Take Full backup

backup database PointInTIME 

to disk ='D:\LearningBackupClass\PointInTIME_Fullbackup.bak' 

with format

 --Take Differential backup

backup database PointInTIME 

to disk ='D:\LearningBackupClass\PointInTIME_MonDiffbackup.bak' with differential

--Take another Differential backup

backup database PointInTIME 

to disk ='D:\LearningBackupClass\PointInTIME_TueDiffbackup.bak' with differential

--Take Transanction/log backup

backup log PointInTIME 

to disk='D:\LearningBackupClass\PointInTIME_Wedtrnbackup.trn' with format

--Take another Transanction/log backup

backup log PointInTIME 

to disk='D:\LearningBackupClass\PointInTIME_thutrnbackup.trn' with format

--Take another Transanction/log backup

backup log PointInTIME 

to disk='D:\LearningBackupClass\PointInTIME_Fritrnbackup.trn' with format

--Restore Database

To Restoring Point-In-Time Recovery

    1.Take full backup.

    2.last Differential backup.

    3.All log backup

--Try whole script carefully you will have complete idea about Point In Time Recovery

restore database PointInTIME 

from disk ='D:\LearningBackupClass\PointInTIME_Fullbackup.bak' 

with norecovery

--Skip Monday differential backup as we take only latest/last differential backup

restore database PointInTIME 

from disk ='D:\LearningBackupClass\PointInTIME_TueDiffbackup.bak' 

with norecovery        

--Includes all log backup          

restore database PointInTIME 

from disk='D:\LearningBackupClass\PointInTIME_Wedtrnbackup.trn' 

with norecovery

restore database PointInTIME 

from disk='D:\LearningBackupClass\PointInTIME_thutrnbackup.trn' 

with norecovery

-- For the final transaction log backup that reaches the point in time, use WITH RECOVERY

restore database PointInTIME 

from disk='D:\LearningBackupClass\PointInTIME_Fritrnbackup.trn' 

with recovery

 Please take note that this example assumes you have previously configured the backup files and that you are aware of the order of transaction log backups required to reach the desired point in time. Prior to implementing point-in-time recovery on a production database, always test your backups in a test environment to make sure they are reliable.