SQL Server Management Studio 2008 - Standard Reports - Schema Changes History - How is the Date Determined?

views:

347

answers:

+1 Q:

SQL Server Management Studio 2008 - Standard Reports - Schema Changes History - How is the Date Determined?

SSMS 2008 comes with a great set of standard reports, including one named Schema Changes History. When I run this report, I see all schema changes made since a certain date. My question is this. How is the date determined? For example, on one database for a particular server, the changes are as of 2/14/2010 1:35:15 pm. But on another database on the same server, the changers are as of 1/5/2010 9:09:15 am. These are not the dates the databases were created.

Thanks.

+1 A:

The report looks into sys.all_objects at the modify_date column or it looks into the default trace for changes, depending on whether the default trace option is enabled or not. Using the default trace has the advantage that it can show dropped objects, but it doesn't work if the default trace was disabled.

Given that the default trace is a roll-over trace the age of the oldest record in it will vary from server to server, based on the amount of DDL changes done on each server.

Remus Rusanu 2010-02-15 18:53:19

@Remus - Thank you. Do you know if the default trace information is persisted anywhere that I could get at it and store it permanently in my own table?

Randy Minder 2010-02-15 19:10:35

Use `SELECT ... FROM ::fn_trace_gettable(...);` to open the default trace .TRC file as a table. Use sys.traces to find the location of the default trace .TRC file(s).

Remus Rusanu 2010-02-15 19:35:24

BTW, if you want to retain DDL changes you should look also into the built-in auditing of SQL 2008: http://msdn.microsoft.com/en-us/library/dd392015.aspx and also consider using EVENT NOTIFICATIONS to monitor DDL changes: http://msdn.microsoft.com/en-us/library/ms189453.aspx

Remus Rusanu 2010-02-15 19:37:57

ansaurus

tags:

views:

answers:

SQL Server Management Studio 2008 - Standard Reports - Schema Changes History - How is the Date Determined?

related questions