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

Published: 19 March 2022
on channel: Right to Learn @BK

21,665

298

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 in SQL Server |...

---------------------------------------------------------
****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)

Watch video How to restore deleted transactions in SQL server | PITR | Ms SQL online without registration, duration hours minute second in high quality. This video was added by user Right to Learn @BK 19 March 2022, don't forget to share it with your friends and acquaintances, it has been viewed on our site 21,665 once and liked it 298 people.

485