Ways to Find Database Changes in SQL Server

Overview:

It is a fact that a program needs to store the value of data in database of the program before it is altered. It means that we constantly need to save the history of alterations made to the database. It is usually required for security reasons. It also maintains the integrity of files and data present on the server. Manually tracking each change in server is a tedious task to do.

There are a number of methods to find database changes in SQL Server like triggers, SQL schema change history report, etc. We will discuss these methods in detail.

#1 Find Database Changes By Change Data Capture:

After update’, ‘After insert’ and ‘After delete’ triggers of SQL server 2005 almost settled the issue of tracking changes in SQL server. However, SQL Server 2008 equipped an even more better method called Change Data Capture (CDC). CDC has a potential to deliver server data archiving and capturing to the developers without any programming. It tracks alterations in user created tables and stores them in relational tables. It can be retrieved by the help of regular T-SQL. CDC involves the following three features.

Mirroring of Database:

Follow the steps to perform this:

  • Assure that SQL server agent is running on the mirror.
  • After the principal has failed over to mirror, create a capture and cleanup job.

Transactional Replication:

sp_replcmds is used to read changes from transaction log.

Attach or Database Restore

Use KEEP_CDC option to retain CDC during the restore of database.

CDC Enabling on a Database:

Enable CDC on Database

By running this, entire database name along with column depicting CDC enable/disable status will be shown.

  • To enable CDC in AdventureWorks database, run the following stored procedure. After CDC gets enabled, results will be shown in SSMS.

CDC result

In this database, a new schema of CDC will be created along with some system tables.

CDC Enabling on One or More Database Tables:

  • To enable CDC on a table-level database, run the following query:

CDC Enable On Multiple Database

The query will return the result indicating whether CDC is enabled or not.

  • Consider a table HumanResources.Shift. Now let us perform insertion operation on this by the following query:

Track data changes In SQL Server

Advantages of Using Change Tracking or CDC:

  • There is a decrease in development time as SQL Server 2016 has functionalities available.
  • No Schema changes needed. Adding tables, triggers, columns, for tracking information is not required.
  • An automatic built-in cleanup mechanism is present to find changes in the server.
  • For obtaining change information, functions have been provided.
  • Overhead is low to DML operations.
  • SQL Server 2016 has SQL Server Management Studio, standard DDL statements, catalog views, and security permissions tools to manage SQL servers.
  • Order of changes depends on time of transaction commit. This yields trusted outcomes for long-running and overlapping transactions./li>

#2 Track Data Changes By SQL Server DDL Triggers

DDL triggers are a solution to find database changes in SQL Server. In this method, snapshot of present database objects is taken and then all the DDL changes are logged. By this, state of any object can be easily tracked.

#3 Track Data Changes By Schema Change History

By making use of SQL Management Server Studio and Schema Change History, one can easily track changes in SQL database by following steps:

Server node > Right-click > Reports > Standard Reports > Schema Changes History

After this, a report is generated showing the latest changes that have been made in the database from last some time.

The Bottom Line

Tracking changes and monitoring activities of SQL server database is very crucial. It safeguards the data present on SQL servers. In this article, we have summed up some of the efficient solutions to find database changes in SQL Server. Users can pick a method, according to their requirement and scenario.