Point-in-time recovery in SQL Server

 Hey guys, In this blog we will discussed about point-in-time recovery in SQL Server and how to perform it and also we discussed on full, differential and transaction log backup.

1.       Full backup – A Full backup is a complete backup of a database. The full backup contains all the  data in a database and can be used to do a complete restore of the database to the point-in-time that   the full backup completed, less than the uncommitted transaction in flight at that time. So, all files,  objects, tables are copied over to a secondary storage target each time. If you perform a full  backup once a day- then everything is copied over once a day.

2.      Differential backup – Differential backup makes a copy of files that have changed since the full  backup.

3.     Transaction Log backup – A transaction log backups copies the transaction log records off to a backup file. It helps to do a restore to a point-in-time other than full or differential backups are taken.

Point-in-time recovery in SQL Server

Point-in-time recovery allows to restore a database into a state it was in any point of time. This type of recovery is applicable only to databases that run under the full or bulk-logged recovery model.

Procedure to perform point-in-time recovery –

STEP -1: First, you have to connect to your instance and open a new query window and write a syntax

Fig-1

STEP -2: Create a table and insert some data as shown below,

Fig-2 

STEP -3: Now, take a full backup  

Fig-3

STEP -4: After that insert some data for differential backup and note down the completion time.

Fig-4

STEP -5: Now, take a differential backup for above inserted data.

Fig-5

STEP- 6: Insert data for performing a log backup as shown below,

Fig-6

STEP- 7: Now take a log backup for above data and note down the completion date and time,

Fig-7

STEP-8: Now, execute a Syntax select *from emp_tbl to get the complete table as shown below,

Fig-8

STEP- 9: Now, drop the database so that you can restore a database because same database is already lies into the database.

Fig-9

 STEP- 10: Now restore a database till point of time.

                   First restore a full backup by using a syntax.

Fig-10

 STEP- 11: Now restore a differential backup by using a below syntax,

                     First restore a full backup with norecovery and then restore a differential backup with recovery

Fig-11

 STEP- 12: Now restore a log database by using a below syntax,

                  For this restore, you have to restore a full backup and differential backup with 

                  norecovery and than log backup with recovery.

Fig-12

STEP- 13: Now restore another log backup by using a below syntax,

Fig-13

STEP- 14: Now restore a database till point of time.

                    At point of time restore, you can restore the data at any period of time.   

Fig-14

STEP- 15: Result,

Fig-15