List databases modified database between two dates in SQL Server 2008

Question

I am trying to get the list of databases modified between 2 dates in SQL Server 2008.

I tried this query,

select * 
from sys.objects            
where modify_date between '2015-12-01' and GETDATE()

but the modify_date column in said query changes only when design of table is changed. If data is inserted to the table it will not be covered.

I have checked the Default trace option in SQL Server, it is ON (set to 1) for the databases.

Is there any method to list the databases which are modified between 2 specified dates?

Thank you in advance.

Only thing I can think off to catch data changes would be to look towards the transaction log and increase i — Allan S. Hansen, Dec 07 '15 at 14:30
Transaction log gives the modification details from SQL service start to SQL service stop. In my case SQL service restarts daily, so if I want to get data modification in last 7 days it is not possible. — IT researcher, Dec 08 '15 at 06:28

score 0 · Answer 1 · answered Dec 08 '15 at 04:37

0

To monitor data you should look into CDC ( change data capture)

Change Data Capture records INSERTs, UPDATEs, and DELETEs applied to SQL Server tables, and makes a record available of what changed, where, and when, in simple relational 'change tables' rather than in an esoteric chopped salad of XML. These change tables contain columns that reflect the column structure of the source table you have chosen to track, along with the metadata needed to understand the changes that have been made. Pinal Dave explains all, with plenty of examples in a simple introduction.

answered Dec 08 '15 at 04:37

Jayanti Lal

1,175
6
18

For Express edition CDC is not available. Can I have some techniques to monitor database modification in SQL express addition. – IT researcher Dec 08 '15 at 06:57
then you can use triggers..also before triggers and after triggers – Jayanti Lal Dec 08 '15 at 07:09
you can go through http://stackoverflow.com/questions/4574010/how-to-create-trigger-to-keep-track-of-last-changed-data – Jayanti Lal Dec 08 '15 at 07:11
for CDC a very good document...https://www.simple-talk.com/sql/learn-sql-server/introduction-to-change-data-capture-(cdc)-in-sql-server-2008/ – Jayanti Lal Dec 08 '15 at 07:12

List databases modified database between two dates in SQL Server 2008

1 Answers1