I have a database more than hundred tables. I am continuously adding columns to existing tables (if required) and also added few new tables.
Now I want to check what changes I have made in last 3 months. Is there any activity log in MS SQL Server 2012 for that specific database to track changes.
6 Answers
Answers 1
Right now,your options are limited ,going forward you can try below and also check to see if they help you now..
1.If you have enabled Audit,you can track the changes
2.Default trace also captures tables created ,but this uses, roll over files mechanism to override last files when the space is full,so you may be out of luck(since you are asking for three months range),but give it a try..
3.Finally One final option is to query Tlog
select * from fn_dblog(null,null) where [transaction name]='CREATE TABLE' the above Tlog option works only if you have Tlog backups for over three months and also you need to restore them
Answers 2
To Check all the activities in past time, you can work with MSSQL Audit. Its the best way to track any changes at any time. Please Check https://msdn.microsoft.com/en-us/library/cc280386.aspx
Answers 3
Perhaps this can get you partway. sys.objects has create and modify dates but unfortunately sys.columns does not. However the latest columns added will have higher column_ids. I don't know that you would be able to pick out deleted columns that easily. Note that changes other than column changes can be reflected by the modify date.
select s.name [schema], o.name [table], o.modify_date [table_modify_date], c.column_id, c.name from sys.schemas s join sys.objects o on o.schema_id = s.schema_id left join sys.columns c on c.object_id = o.object_id where o.type = 'U' --user tables only and o.modify_date >= dateadd(M,-3, getdate()) order by s.name, o.name, column_id; To make this audit easier in the future you can create a DDL trigger that will log all schema changes to a table or in source control if you use something like a SSDT data project to manage your changes.
Answers 4
You could use a DDL Trigger:
CREATE TRIGGER ColumnChanges ON DATABASE FOR ALTER_TABLE AS DECLARE @data XML SET @data = EVENTDATA() INSERT alter_table_log (PostTime, DB_User, Event, TSQL) VALUES (GETDATE(), CONVERT(nvarchar(100), CURRENT_USER), @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ; GO Answers 5
Take snapshots of the metadata definitions via the "Generate Scripts..." Tasks option from the SQL Server Management Studio.
Store the generated script files in a folder whose name references the current date. Once this has been done more than once, WinDiff can be used to highlight the database changes made between any two snapshots. Choose the "Generate Scripts" options carefully and consistently so that time based comparisons are more beneficial.
Answers 6
You could run a report from the right click menu on the DB:
There are several reports that might interest you in this drop down. Or you could possibly create a custom report with just the information that you need.
My Schema report only goes back to 9/3/2016, but I have 1000+ tables with 60+ columns with many updates daily. Yours might go back further.
0 comments:
Post a Comment