none
Database schema changes

    Question

  • Can have SQL script for changes made on database schema from a specific date? in SQL sever 2000 and SQL server 2005

    Saturday, October 27, 2007 6:53 AM

Answers

  • By defalt SQL Server does not keep any log for schema changes. Yes there are tools like SQL Profiler which can track these activities. But its not a permanent solution. In SQL Server 2005 you can create DDL trigger which track all the schema changes and  it will not be a performance issue as SQL Profiler. But in 2000 , you have to run SQL Profiler there is no DDL option .  So you need to run profiler and store the data in File or table which can be queried later

     

    Madhu

    Saturday, October 27, 2007 7:16 AM
    Moderator

All replies

  • By defalt SQL Server does not keep any log for schema changes. Yes there are tools like SQL Profiler which can track these activities. But its not a permanent solution. In SQL Server 2005 you can create DDL trigger which track all the schema changes and  it will not be a performance issue as SQL Profiler. But in 2000 , you have to run SQL Profiler there is no DDL option .  So you need to run profiler and store the data in File or table which can be queried later

     

    Madhu

    Saturday, October 27, 2007 7:16 AM
    Moderator
  • In SQL Server 2005, you have Standard Reports built-in by Default and one of the reports is "Schema Changes History". This should help you to a certain extent.

     

    Also, by default SQL Server 2005 maintains a profiler trace file from the server startup. The events under these two objects should be helpful also:

     

    Objects

    Security Audit

     

    For SQL 2000, you could emulate the default SQL Server 2005 trace and have the stored procedure run every time the server starts up to initiate a default trace or write DML triggers.

     

    HTH

    Monday, October 29, 2007 1:27 PM
  • It was my bad.. i forgot about defualt trace in sql server. this may help you

     http://madhuottapalam.blogspot.com/search?q=Default+Trace+in+SQL+Server+2005

     

    Madhu

    Monday, October 29, 2007 1:32 PM
    Moderator