sqlsql-serverdatabaset-sql

Check for changes to a SQL Server database?

is there a way to get database checksum using system SQL Server functions?

Or other way to quickly figure out if there are any changes in database?

I do not want to use any specific database analysis software for this.

I look for any changes in database (schema/object changes, data changes).

I use SQL Server 2008.

Solution

  • You might find the information in the transaction log...

    Use

    DBCC LOG(<database name>[,{0|1|2|3|4}])

    0 - Basic Log Information (default)

    1 - Lengthy Info

    2 - Very Length Info

    3 - Detailed

    4 - Full

    Example:

    DBCC log (MY_DB, 4)

    The result is somewhat cryptic and is better used with a third-party tool thought...

    Credits: http://www.mssqlcity.com/Articles/KnowHow/ViewLog.htm

    EDIT:

    You might try 

    Select * from ::fn_dblog(null, null)

    This page contains some usefull information about the results returned by the functions... http://www.novicksoftware.com/udfofweek/Vol1/T-SQL-UDF-Volume-1-Number-27-fn_dblog.htm

    Keep in mind, that those procedures are not for the public and therefore not easy to understand. The other possibility is to add triggers on all tables. But that is on the otherhand a lot of work.