I want to check SQL server database is modified or not. Reason to check database is modified is for backup(sql database backup) purpose. So I will be doing a full backup first and if any data inserted, modified or any structural or any changes done then I have to identify that there is some changes in databases and backup the database.
The checking for changes in database may be done based on frequency(daily, hourly etc). Modified date of the mdf or ldf file can't be used to check database is modified or not. Differential backup only backups changes since last backup but it's different and it does a backup even if there is no changes since last backup but size will be very less(in kb which you can test easily) .
In windows NTFS file system there exists USN journal for each file by using it we can check file is modified or not and then we can do backup the file if modified. Which is also implemented in most of the backup software.
So how can I check whether any changes done to database after my last backup, so that i can do backup if any chnages are detected?