Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2005
    Posts
    7

    Unanswered: Detecting Database Changes

    Is there a way in detecting changes in the database?

    thanks..

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    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.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Jan 2005
    Posts
    7
    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.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Uhm....(asking with trepidation)...why?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Posts
    492
    Recurring every 1 minute... giving me shivers.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Assume it changes. What are you going to do then?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jan 2005
    Posts
    7
    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.

  8. #8
    Join Date
    Jan 2005
    Posts
    7
    What are the chances that a disk will crash.. which will be unrecoverable.
    Last edited by FartKnocker; 01-11-05 at 21:17.

  9. #9
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Lightbulb

    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 for more information.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  10. #10
    Join Date
    Jan 2005
    Posts
    7
    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?

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Jan 2005
    Posts
    7
    hahahaha... i just hope they won't do that.

    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

  13. #13
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Red face

    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?!
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  14. #14
    Join Date
    Jan 2005
    Posts
    7
    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.

  15. #15
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •