Pavlo Neiman
Pavlo Neiman

Reputation: 7536

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.

Upvotes: 3

Views: 14884

Answers (7)

Husni Salax
Husni Salax

Reputation: 2020

You can use XEvent Profiler if you are using SQl Management Studio.enter image description here

Here you find each executing command on your database.

Upvotes: 0

Anton Shepelev
Anton Shepelev

Reputation: 1011

I came to comp.databases.ms-sqlserver with exactly the same question, because I was developing a back-up solution for a system with many databases, of which but a minority are in active use at any given time, so that the typical strategies of regular backup are a terrible waste of time, space, and calculation power.

With some help, I came up with checking the transaction log via the undocumented function fn_dblog, the output whereof is reset after every full or differential backup, potentially deleting entries about data changes. If this is tolerable, you can do something like the following:

-- A table to store the last LSN read the previous time:
-- CREATE TABLE last_change( lsn CHAR(22) NOT NULL )

DECLARE @prev_lsn CHAR(22), -- the previous LSN
        @last_lsn CHAR(22)  -- the last     LSN

-- Read the previous LSN from storage:
SELECT @prev_lsn = lsn FROM last_change

-- Read the last LSN about data change subsequent to @prev_lsn:
SELECT TOP 1 @last_lsn = [Current LSN]
FROM fn_dblog(DEFAULT,DEFAULT)
WHERE
    operation IN
    -- Filter the events about actual data change:
    ( 'LOP_INSERT_ROWS', 'LOP_MODIFY_ROW', 'LOP_DELETE_ROWS') AND
    -- Ignore changes to system objects:
    AllocUnitName NOT LIKE 'sys.%'                            AND
    -- Ignore changes to our own table to avoid an infinite loop:
    AllocUnitName NOT LIKE 'dbo.last_change%'                 AND
    -- Ignore entries preceding the last known LSN:
    [Current LSN] >= COALESCE(@prev_lsn,'')
ORDER BY [Current LSN] DESC

IF @last_lsn IS NULL AND @prev_lsn IS NOT NULL BEGIN
    PRINT 'Transaciton log has been reset. Data may have changed.'
    DELETE FROM last_change
    GOTO Done END

IF COALESCE(@last_lsn,'') = COALESCE(@prev_lsn,'') BEGIN
    PRINT 'Data has not changed.'
    GOTO Done END;

-- Changes in the database found. Update the stored LSN value:
IF @prev_lsn IS NULL
    INSERT INTO last_change    (lsn) VALUES( @last_lsn ) ELSE
    UPDATE      last_change SET lsn  =       @last_lsn

-- Handle changes:
PRINT 'Data has changed.'

Done:

Bear in mind, however, that, being undocumented, the fn_dblog function may change or disappear any time without prior notice, breaking this code.

Upvotes: 0

Karlossus
Karlossus

Reputation: 83

By "changes" I had a similar problem but not changes to the data, rather changes to the schema. I created the following function to generate a checksum of the whole database schema based on INFORMATION_SCHEMA. Expand to additional information schema tables if your system uses them, this is enough for my usage.

I've used this to create a daily alert to notify me of any changes to our production database, so it's become easy to police unplanned/unauthorised changes by other team members. Simply store the checksum, and then run the function and compare to the stored value. If different, then someone has changed something.

CREATE FUNCTION [dbo].GetDbSchemaChecksum 
(   
)
RETURNS int
AS
BEGIN
    declare @retVal int
    set @retVal = 
    (
    SELECT 
        CHECKSUM_AGG(T.Chk) DbSchemaChecksum
    FROM
        (
        select checksum(TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE) Chk from INFORMATION_SCHEMA.TABLES
        union all
        select checksum(TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, DATETIME_PRECISION) Chk from INFORMATION_SCHEMA.COLUMNS
        union all
        select checksum(CONSTRAINT_SCHEMA, CONSTRAINT_NAME, UNIQUE_CONSTRAINT_CATALOG, UNIQUE_CONSTRAINT_SCHEMA, UNIQUE_CONSTRAINT_NAME, MATCH_OPTION, UPDATE_RULE, DELETE_RULE) Chk from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
        union all
        select checksum(CONSTRAINT_NAME, TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, IS_DEFERRABLE, INITIALLY_DEFERRED) Chk from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
        union all
        select checksum(TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_SCHEMA, CONSTRAINT_NAME) Chk from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
        union all
        select checksum(CONSTRAINT_SCHEMA, CONSTRAINT_NAME, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION) Chk from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        union all
        select checksum(CONSTRAINT_SCHEMA, CONSTRAINT_NAME, CHECK_CLAUSE) Chk from INFORMATION_SCHEMA.CHECK_CONSTRAINTS 
        union all
        select checksum(TABLE_SCHEMA, TABLE_NAME, VIEW_DEFINITION, CHECK_OPTION, IS_UPDATABLE) Chk from INFORMATION_SCHEMA.VIEWS
        union all
        select checksum(VIEW_SCHEMA, VIEW_NAME, TABLE_SCHEMA, TABLE_NAME) Chk from INFORMATION_SCHEMA.VIEW_TABLE_USAGE
        union all
        select checksum(SPECIFIC_SCHEMA, SPECIFIC_NAME, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION) Chk from INFORMATION_SCHEMA.ROUTINES
        union all
        select checksum(SPECIFIC_SCHEMA, SPECIFIC_NAME, ORDINAL_POSITION, PARAMETER_MODE, IS_RESULT, AS_LOCATOR, PARAMETER_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH) Chk from INFORMATION_SCHEMA.PARAMETERS
        ) AS T
    )

    return @retVal

END
GO

Upvotes: 1

Frison Alexander
Frison Alexander

Reputation: 3256

I've found that SQL Server 2008+ Enterprise edition has a feature called Change Data Capture

This will capture any insert, updates, deletes on the database. Unfortunately I don't have Enterprise edition to test this out. I'll add links here so that it will be of help to someone in the future.

Enable/Disable Change Data Capture

Good writeup on how to use this feature

EDIT: For the Standard Edition there is Change Tracking.

Related Questions: Auditing SQL Server data changes

Upvotes: 0

Yves M.
Yves M.

Reputation: 3318

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.

Upvotes: 4

Jaykay
Jaykay

Reputation: 666

Changes in the form of updates to tables in the form of updates/insertions/deletions can be tracked by use of a trigger which would update the timestamp in a master table. We have to maintain a table containing the name of tables and last updated time. Any change in the individual table would update this table in the corresponding row!

Schema changes can also be handled by way of triggers which would update the table of tables. May be in you case, we can have additional column(s) with BOOL data type that would specify if the change was in schema or the data.

Upvotes: 0

AdaTheDev
AdaTheDev

Reputation: 147324

One approach to record/log schema changes is to use DDL triggers as of SQL Server 2005.

You can set them up on a database to fire on specific types of schema changes (e.g. CREATE_PROCEDURE, ALTER_PROCEDURE) and then maintain whatever log you want to.

Upvotes: 1

Related Questions