Detecting Database Changes

Is there a way in detecting changes in the database?
thanks..Do you mean structure changes (by DDL statements) or data changes (by DML statements)?

The first category is handled by making a copy of your schema information, and comparing it with the current schema information.

The second category has to be programmed by yourself, using INSERT, UPDATE and DELETE triggers.|||both structure and data changes for the entire database...

Coz I'm planning to create a service that wakes up every 1 minute and check if there are changes in the database.|||Uhm...(asking with trepidation)...why?|||Recurring every 1 minute... giving me shivers.|||Assume it changes. What are you going to do then?|||if it changes then it will perform a log back up...

what's the best plan to avoid data loss when you have a disk crash? The thing that you can't recover any data from your disk.|||What are the chances that a disk will crash.. which will be unrecoverable. :confused:|||It happens more than once to me, so it is a real potential risk. For a critical system, you must have a proper backup & restore plan, considering the down-time, which must be acceptable for your business.

Ever thought about SQL Server's replication possibilities? Maybe this answers your questions, instead of re-inventing the wheel. See Microsoft (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/replover_694n.asp) for more information.|||yes i have thought of Replication... but the client only wanted to use MSDE. :(

and i think MSDE has limitations on replication, like the publisher and the distributor must be in the system computer. am I correct on this one?|||So your client wants 100% of the functionality, but doesn't want to pay for it.
...but is going to pay you for the hours and hours it will take to duplicate this functionality, and for the hours and hours it will take to maintain it...

Yeah, I've worked for that client before too. Be careful, they don't always pay their bills.|||hahahaha... i just hope they won't do that. :mad:

btw is this solution feasible. after a full backup, a log backup will occur every 1 minute, and after 10 minutes a incremental/differential backup will take place and deleting the previous log backups then the log - diff backup cycle continues.

e.g.

day 1
Full - log - diff - log - diff
day 2
Full - log - diff - log - diff|||You are discussing different solutions, but it would help to know the problem. What do you (or your client) want to achieve? Back-upping the data frequently on the same disk does not help if the disk crashes, and has to additional value in case your server stops in the middle of something and has to be restarted, because the server will recover automatically. So, again: what's the idea, except spending facturable hours?!|||the main problem is backing up frequently... but i want to back it up if there are changes... and of course it has to be backed up on a different disk.|||How much data does your client feel is OK to loose in terms of time? 's the deciding factor for the frequency of transaction log dumps. And having differential mixed with transaction dumps is just too kinky, it won't give you anything more than just the transaction dump by itself. In fact, for point-in-time recovery you can't even use differential. Based on your "cry for help" and this "every minute wake-up call" to do the log dump, why don't you go ahead and do every minute log dump with nightly full backup. However, you will also have to have your tape drive running every minute too, because if the disk that holds the backup devices crashes, - what do you have at the end, even if you backup every second?