1

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?

Improve this question
13
  • 8
    What are you really gaining out of this approach? What's wrong with small differential backups? Storage space is cheaper than dirt, and file system management is easy. I don't see how the juice is worth the squeeze here.
    – Thomas Stringer
    Commented Dec 30, 2014 at 14:07
  • 1
    If the differential file is really small then why does it matter? Differentials give you everything that changed since the last full backup. You shouldn't selectively not do differentials, if they are very tiny then SQL uses negligible resources to actually perform the backup. We take differential backups on our DBs that, sometimes, have little to no activity.
    – Kris Gruttemeyer
    Commented Dec 30, 2014 at 14:07
  • 2
    I'm still not seeing the end game here. If there was no activity and you do a backup, the filesize is incredibly small, so what's the deal? Is drive space an issue? I have 100s of transaction log files that are < 200k in size because there was no activity.
    – Kris Gruttemeyer
    Commented Dec 30, 2014 at 14:33
  • 4
    Why take the time and develop a solution that only backs up when changes are made and is subject to human error? Why not just take backups at set intervals (regardless of how much/any activity)? If drive space isn't an issue, just take the backups at set intervals, go get some coffee and continue on your day. You're troubleshooting and improving something unnecessarily, it seems.
    – Kris Gruttemeyer
    Commented Dec 30, 2014 at 14:45
  • 2
    Save disk space for what? Are you going to put other things there? What's going to happen when you need it because your data is suddenly changing daily?
    – Aaron Bertrand
    Commented Jan 2, 2015 at 14:51

2 Answers 2

Reset to default
14

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.

Improve this answer
5
  • 3
    In other words the database and other products already do this for you; use them. Don't reinvent the wheel.
    – Ben
    Commented Dec 30, 2014 at 18:59
  • There is another case. Think of scenario where i take only full backups daily or hourly. So if database is not modified for about a week then taking a backup everyday is waste of disk space.But I may not be knowing about database changes is done or not. So in this case it will be helpful if i know changes are actually done or not and then perform backup so that a lot of disk space can be saved.
    – IT researcher
    Commented Jan 2, 2015 at 7:24
  • 1
    @ITresearcher That doesn't make much sense to me at all. You're going to take full backups every hour instead of log backups, and then worry about wasting disk space? shrug sorry, I give up, because I don't understand why you have a problem to solve here.
    – Aaron Bertrand
    Commented Jan 2, 2015 at 12:19
  • @AaronBertrand Sorry. it may be daily backup ,not just hourly. What i am trying to say is backup should be proper and it should not have more duplicate or unwanted data. As i said before if nothing changed in database for a week then taking daily full backup means 7 copies of backup have same content. So it can be avoided.
    – IT researcher
    Commented Jan 2, 2015 at 12:25
  • 2
    @ITresearcher You haven't convinced me that this is worth pursuing. If you can't afford disk space for 7 days' worth of backups, then what are you going to do if data does change every day?
    – Aaron Bertrand
    Commented Jan 2, 2015 at 14:49
3

Sure, you can hook into some mechanism to watch for changes, either at the db- or fs-level. And your code will probably work correctly.. for a while, at least. But then, someday, someone will add code to do something additional that you weren't expecting. Which may not matter. And it will happen again, and again. Soon, all kinds of data is being changed that your algorithm isn't aware of. Oops.

Don't do this. Please. I'm sure you can if you want to. You will likely even get it to work. But it really is over-refactoring. If you don't end up regretting implementing it, somebody at your company will.. some day. There be dragons in db's.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.