How to restore deleted transactions in SQL server | PITR | Ms SQL

Backups keeps your data safe. If you know when your data was deleted then you can do point in time restore using the timestamp during the restore operation.
But, if you do not know the date and time when the data is deleted, then please do watch this video to find the solution.
How to restore a database to a Specific Point in time. Watch the video using below link
• Point in time restore ...
---------------------------------------------------------
****Scripts used in this video*****
use DB123
create table test (ID varchar(10), Name varchar(10))
select * from test order by id
insert into test values ('A101', 'Record 1')
insert into test values ('B102', 'Record 2')
backup database DB123
to disk = 'F:\dbbackup\DB123_full.bak'
---------------------------------------------------------
insert into test values ('C103', 'Record 3')
insert into test values ('D104', 'Record 4')
backup database DB123
to disk = 'F:\dbbackup\DB123_diff1.bak' with differential
----------------------------------------------------------
insert into test values ('E105', 'Record 5')
insert into test values ('F106', 'Record 6')
delete from test where name = 'Record 4'
backup log DB123
to disk = 'F:\dbbackup\DB123_log1.trn'
-----------------------------------------------------------
insert into test values ('G107', 'Record 7')
insert into test values ('H108', 'Record 8')
backup log DB123
to disk = 'F:\dbbackup\DB123_log2.trn'
---------------------------------------------------------------
restore database DB123_temp
from disk = 'F:\dbbackup\DB123_full.bak' with norecovery,
move 'DB123' to 'F:\dbbackup\DB123_temp_data.mdf',
move 'DB123_log' to 'F:\dbbackup\DB123_temp_log.ldf'
restore database DB123_temp
from disk = 'F:\dbbackup\DB123_diff1.bak' with norecovery
--If we know exact timestamp
restore log DB123_temp from disk = 'F:\DbBackup\DB123_log1.trn'
with recovery, STOPAT = '2022-03-03 16:59:00'
--Not sure about timestamp
Select [Current LSN], [Transaction ID], Operation, Context, AllocUnitName
FROM fn_dblog(NULL, NULL)
WHERE Operation = 'LOP_DELETE_ROWS'
SELECT [Current LSN], [Transaction ID],[Operation],[Context], [AllocUnitName]
FROM fn_dblog(NULL, NULL)
WHERE [Operation] = 'LOP_DELETE_ROWS'
AND [AllocUnitName] = 'dbo.test'
--If there is only one transaction ID under which all DELETED rows are showing
--that means this action has been performed in a single batch.
SELECT
[Current LSN], Operation,[Transaction ID],[Begin Time],[Transaction Name],[Transaction SID]
FROM fn_dblog(NULL, NULL)
WHERE [Transaction ID] = '0000:00000379'
AND [Operation] = 'LOP_BEGIN_XACT'
restore log DB123_Temp
from disk = 'F:\dbbackup\DB123_log1.trn'
with stopbeforemark = 'lsn:0x0000002e:000001c0:0001', norecovery
restore log DB123_Temp
from disk = 'F:\dbbackup\DB123_log1.trn'
with stopbeforemark = '46000000044800001'
restore log DB123_Temp
from disk = 'F:\dbbackup\DB123_log2.trn' with recovery
insert into DB123.dbo.test
select * from DB123_temp.dbo.test
where name not in (select name from DB123.dbo.test)

[@rajendraprasad9040]

Illustration easily understandable...good

[@yoga3957]

Very neatly explained👍

[@kandavel0588]

Excellent instruction bro

[@Jayadhev]

Good explanation brother..

[@sagarkashid123]

Nice vedio please make vedio ag latency troubleshoot and wat action need to take.also one vedio for MAXDOP and Cost threshold parameter settings how to calculate and assign

[@subhashyadav9262]

Excellent

[@paragnimbhore9963]

Hiii exactly follow same steps as you said.... But now when I run fn_dbo with operation delete rows..... It shows blank output..... Even I tried that entry is deleted from my original table..... Can you tell what exactly is issue is and how I can fix it

[@ramut.l8897]

Hi sir, I accidentally executed the delete command on the user table and lost all records from my client's live server and I don't have a backup, is there any way to recover the data as the above method is not possible since I don't have any backup and log files

[@paragnimbhore9963]

Hiii when I am trying to restore the log backup at temp databse it shows the popup that 'the backup set holds backup of a databse other than the existing 'db123_Test' databse.

[@paragnimbhore9963]

Can you tell me where I am wrong exactly

[@sqlserver6395]

Do video on ag, how to collect latency, how we can check it and trouble shoot it.

[@rvsland.gansol]

halo sir, this process can be done with a database that uses the recovery model: "simple" ??

[@debasishsahoo3446]

Simple recovery model has no log backup.. Without log backup how can do

[@paragnimbhore9963]

How exactly you convert the lsn hexa decimal format into decimal..... May you please explain

[@righttolearnbk]

Please refer byjus.com/maths/hex-to-decimal/

[@sathyakathir5242]

Pls post video on creating linked servers.

[@righttolearnbk]

czcams.com/video/3hBNGnFVxP4/video.html

[@sathyakathir5242]

@@righttolearnbk thnks a lot sir....