How to get the last 30 days of records?

Question

I'm working on MSServer and I have a specific table that I want to retrieve inserted or modified data for the last 30 days. My table doesn't contain thing to save the creation date and I'm wondering if there is some trace in Master database or something that can help. Thanks.

If you backed up 30 days ago we could see what the max value of an auto increment column was. Then this value could be used on the current table to restrict to the records you want. — Tim Biegeleisen, Apr 26 '16 at 10:54
This ***may*** help you roll a database back a certain duration; http://stackoverflow.com/questions/16945254/how-to-perform-sql-roll-back-from-transaction-logs Pretty drastic brute force approach, depends on how critical it is to you... — MatBailie, Apr 26 '16 at 11:05

score 1 · Answer 1 · answered Apr 26 '16 at 10:48

1

There's no proper way to do it if you don't have indication, when row was added.

answered Apr 26 '16 at 10:48

Evaldas Buinauskas

13,739
11
55
107

1

I think this aswer is a possible comment. If you want, convert it – Joe Taras Apr 26 '16 at 11:01
@JoeTaras It's quite harsh one, but it's kinda right. I don't need an upvote. I had doubts whether it should be a comment or an answer and decided to use latter one. – Evaldas Buinauskas Apr 26 '16 at 11:08
Dear, I don't suppose you want an upvote. I've simply express my opinion. – Joe Taras Apr 26 '16 at 11:11
This is the correct answer to "I wonder if there is a way to do it" – Nick.Mc Apr 26 '16 at 12:42

score 0 · Answer 2 · answered Apr 26 '16 at 12:25

You can try to access the transaction log and get a list of all insert-transactions sorted by time stamp. Then find the time stamp of the last inserted row of the last month. These will lead you (hopefully) at the end to the last inserted row of last month (all rows with higher ID will be the data your are looking for).

Step by step (maybe you need to make some adjustments to the queries depending on your situation):

Get the transactions:

declare @TableName sysname

set @TableName = 'Table_1'

select [Current LSN],
[Operation], 
[Transaction ID], 
[Parent Transaction ID],
[Begin Time], [Transaction Name],
[Transaction SID]
from fn_dblog(null, null)
where [Operation] = 'LOP_BEGIN_XACT'
and  [Transaction ID] 
in
(   
    -- get transaction id's from your table (inserts)
    SELECT [Transaction ID]
    FROM fn_dblog(NULL, NULL) 
    WHERE 
    Operation = 'LOP_INSERT_ROWS'
    and
    AllocUnitName LIKE '%' + @TableName + '%'
)
order by [Begin Time] ASC

Get the last transaction:

    SELECT *
    FROM fn_dblog(NULL, NULL) 
    WHERE Operation = 'LOP_INSERT_ROWS'
    and [Transaction ID] = 'XXX' -- your transaction id

In den Row Log Content Rows you find the value of your data of the last inserted row of the last month. Now you only need to convert the inserted data into "readable" data according to the following website:

http://www.sqlshack.com/reading-sql-server-transaction-log/

Now after converting the inserted data back into "readable" data, you should be able to find the relevant last row of last month inserted in your table. All rows with higher ID than that are inserted later and therefore the data you are looking for.

How to get the last 30 days of records?

2 Answers2