Last post Jul 13, 2017 02:11 PM by limno
Jan 23, 2017 08:12 PM|sureshtalla|LINK
I done some testing on my development machine , but i am unable to get Deleted Records.
where I am doing wrong , please take a look and help me.
Below is the Script and the Steps:
--Step 1: Created DataBase:
CREATE DATABASE RecoverTestDatabase
CREATE TABLE [BlogArticles](
[Article_ID] INTEGER IDENTITY,
[Artilce_Date] DATETIME DEFAULT GETDATE(),
[Article_Creator] NVARCHAR(50) DEFAULT 'Admin');
--Step2 : Inserted Data into the DataBase:
INSERT INTO BlogArticles DEFAULT VALUES;
--Step3: Deleted Data From DataBase:
DELETE FROM BlogArticles
--Step4 Take DataBase Full Backup to Some Other Drive in the Same System:
Say Path : G:\RecoverTestDatabase.bak
--Step 5 : Taken Backup of Tranaction Log File:
FROM fn_dbLog(NULL, NULL)
WHERE [Operation] = 'LOP_DELETE_ROWS'
FROM fn_dblog(NULL, NULL)
WHERE [Transaction ID] = '0000:00000408'
AND [Operation] = 'LOP_BEGIN_XACT'
SELECT CONVERT(INT, CONVERT(VARBINARY, '0x0000001c', 1)) --28
SELECT CONVERT(INT, CONVERT(VARBINARY, '0x000000ce', 1)) --206
SELECT CONVERT(INT, CONVERT(VARBINARY, '0x0001', 1)) --1
SELECT RIGHT('0000000000' + CAST(CONVERT(INT, CONVERT(VARBINARY, '0x000000ce', 1)) AS NVARCHAR), 10)
-- Outputs the decimal for the third section
SELECT RIGHT('00000' + CAST(CONVERT(INT, CONVERT(VARBINARY, '0x0001', 1)) AS NVARCHAR), 5)
RESTORE DATAbase RecoverTestDatabase_COPY
FROM DISK = 'G:\RecoverTestDatabase.bak'
MOVE 'RecoverTestDatabase' TO 'G:\RecoverTestDatabase.mdf'
,MOVE 'RecoverTestDatabase_log' TO 'G:\RecoverTestDatabase_log.ldf'
-- Restore LOG backup with STOBEFOREMARK option to recover exact LSN
RESTORE LOG RecoverTestDatabase_COPY
FROM DISK = 'G:\t2.trn'
STOPBEFOREMARK = 'lsn:28000000020600001'
It throwing some exception below messge:
Processed 168 pages for database 'RecoverTestDatabase_COPY', file 'RecoverTestDatabase' on file 1.
Processed 2 pages for database 'RecoverTestDatabase_COPY', file 'RecoverTestDatabase_log' on file 1.
RESTORE DATABASE successfully processed 170 pages in 0.206 seconds (6.414 MB/sec).
Msg 4335, Level 16, State 2, Line 3
The specified STOPAT time is too early. All or part of the database is already rolled forward beyond that point.
Msg 3013, Level 16, State 1, Line 3
RESTORE LOG is terminating abnormally.
Jan 24, 2017 02:05 PM|PatriceSc|LINK
A specialized SQL Server forum at
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home could be better than a "SQL Server with ASP.NET" oriented forum.
Not an expert but as you are doing a db backup and a log backup without any activity in between my understanding is that SQL Server just sees that the log doesn't change anyhting to the db restore.
I believe you want rather to test a scenario such as :
- doing a db backup
- adding rows
- doing a log backup
- deleting rows
- doing a log backup
and then restore the db up to the first log backup to see that deleted rows are back in your db ???
Jul 13, 2017 04:33 AM|JohnsonWelch|LINK
restoring database using fn_dblog() function is too long process also you need to have the backup of your transaction log. Instead of running this long procedure, there are some other ways too which will better help you to recover
deleted records In SQL Server.
Jul 13, 2017 02:11 PM|limno|LINK
Muhammad Imran has a post for how to recover deleted data from your log file.
Please check it out. It is a good learning process but i will not count on it.