Sql-server – Check if database has changed to determine need to perform backup

sql server

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?

Best Answer

You can't rely on things like journaling or physical file changes to see if data has changed. I hesitate to mention LSNs because I certainly don't want to encourage any attempt down that road. Trying to do this just to determine whether it might not be worth it to actually go ahead and take a log backup is a disaster waiting to happen.

Take full backups on your current schedule. Take log backups such that your recovery objectives are met. For example, if your business stakeholders state that it is not acceptable to have more than 5 minutes of data loss, back up the log every 5 minutes (and assume that disaster can strike at 4:59.997 since the last log backup).

Sorry, but your approach does not make sense. You are trying to optimize something that is already pretty optimal - SQL Server is pretty good about resource usage and creating small diff/log backup files when there have been few or no changes. What you are attempting to do is like trying to get better gas mileage by wiping a bug off your bumper. Just take the backups. I like the analogy Thomas made: "the juice isn't worth the squeeze."

If nothing else, having a log backup file represented for every 5 minute interval will be consistent and easy to automate solutions around. If you skip some of these backups because (you think) "nothing changed", then it will be harder to automate solutions around them, and troublesome to determine if a backup is missing for that reason or because it is actually missing.

Related Question