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.
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:
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:
By running this, entire database name along with column depicting CDC enable/disable status will be shown.
In this database, a new schema of CDC will be created along with some system tables.
CDC Enabling on One or More Database Tables:
The query will return the result indicating whether CDC is enabled or not.
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.
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.
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.