June 16, 2016 at 4:19 am
can you guide me how to recover the data
The full back up was taken at 7:00 AM
Users did the work till 4:30 PM
Records were deleted at 4:45 PM
I want to restore all the records deleted at 4:45 PM
June 16, 2016 at 4:58 am
Hi,
Do you have Db in full recovery?.
do you have log backup?
Regards
Durai Nagarajan
June 16, 2016 at 5:01 am
k2000.rajesh (6/16/2016)
can you guide me how to recover the dataThe full back up was taken at 7:00 AM
Users did the work till 4:30 PM
Records were deleted at 4:45 PM
I want to restore all the records deleted at 4:45 PM
Only if you have a log backup taken anywhere between 4:31PM and 4:44PM, to recover all data.
You can also try reading the log file and find the deleted records if they are a little in count.
June 16, 2016 at 5:37 am
DB is full recovery mode , we dont have any log backups after full back up was taken at 7:00 AM
June 16, 2016 at 6:18 am
take log backup and go for point in time restore on other location to retrieve the data using last full backup and the log backup taken.
Regards
Durai Nagarajan
June 16, 2016 at 6:26 am
VastSQL (6/16/2016)
k2000.rajesh (6/16/2016)
can you guide me how to recover the dataThe full back up was taken at 7:00 AM
Users did the work till 4:30 PM
Records were deleted at 4:45 PM
I want to restore all the records deleted at 4:45 PM
Only if you have a log backup taken anywhere between 4:31PM and 4:44PM, to recover all data.
No, he can use a log backup taken after that time and restore it with the STOPAT option
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 16, 2016 at 6:27 am
k2000.rajesh (6/16/2016)
DB is full recovery mode , we dont have any log backups after full back up was taken at 7:00 AM
What times do you have log backups from?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 16, 2016 at 11:04 pm
If the transaction that you have deleted accidentally were performed within a Begin transaction and did not committed, can be recovered by using fn_dblog() function. You can apply the below command for viewing your accidentally deleted data.
SELECT
[Current LSN],
[Transaction ID],
[Operation],
[Context],
[AllocUnitName]
FROM fn_dbLog(NULL, NULL)
WHERE [Operation] = 'LOP_DELETE_ROWS'
The process of recovering data using fn_dblog function is too long and the function is also a undocumented. However, you can use third party commercial tool like SQL Log File Reader[/url] that can easily recover your data from transaction log file
June 19, 2016 at 11:06 pm
This was removed by the editor as SPAM
June 21, 2016 at 11:42 pm
"We can retrieve the data from Transaction Log table by running the query below.
SELECT
[Current LSN],
[Transaction ID],
[Operation],
[Context],
[AllocUnitName]
FROM fn_dblog(NULL,NULL)
WHERE [Operation]='LOP_DELETE_ROWS'
It will returns some n number of rows. We can also add the table name in the filter condition to which we need to recover the data as shown below.
SELECT
[Current LSN],
[Transaction ID],
[Operation],
[Context],
[AllocUnitName]
FROM fn_dblog(NULL,NULL)
WHERE [Operation]='LOP_DELETE_ROWS'
AND [AllocUnitName]='dbo.TableName'
Using the Transaction ID from the above query we need to retrieve the Log Sequence Number (LSN). We can retrieve the time the Delete statement was executed. Now pass the LSN ID to the below query which returns the value deleted.
SELECT CONVERT (INT,CONVERT(VARBINARY,'0x00000014',1))
SELECT CONVERT (INT,CONVERT(VARBINARY,'0x000000ee',1))
SELECT CONVERT (INT,CONVERT(VARBINARY,'0x0001',1))
"
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply