Is there a way to trace the DDL and DML activities performed on Microsoft SQL Server manually? I had C4 audit enabled to log all the activities previously and it mysteriously turned off. So I was looking for a way to trace changes/updates made to the complete database in terms of it's schema and the data from an year ago to current date.
Most sources pointed towards the data stored in default trace. Unfortunately, it only stores data for last seven days.
I tried using the fn_dblog() and only saw very recent data present. Generating Standard Reports also resulted in same.
So, is there any other way to view this historical data?
I'm guessing your Audit was turned off long enough ago to explain why you're only seeing recent data (couldn't tell you how it was turned off though).
Other tools built into SQL Server that might help you with auditing is Change Data Capture and/or you could also look into DDL Triggers.