Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2008
    Posts
    2

    Unanswered: Query regarding Backups using Idera

    The database administrator in the company that I work for was discussing a backup problem with me. Every night, he kicks off the Idera SQLSafe for server backups. The entire backing up takes about 6 hours or so. He wanted to know if there is a query to check each database as to when a user accessed it and actually did something with it (changed an object etc) so that only those databases get backed-up that have been in use. He says he has been unable to achieve this since technically, every night Idera accesses all the databases so it shows them as recently modified. This way, even those dbs that haven't really been changed by a user are unnecessarily backed-up.

    Would anyone have a solution for this.

    Thanks

    Ritika

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You could do full backups once each week, and then just do differential backups nightly.
    And he could dump Idera and any other 3rd party tools he is using to backup the databases, and instead be a real dba and back them up himself.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Well, I assume they are using Idera for compression and/or encryption purposes.

    I'd go for diff backups for a couple of days, but then interrogate backupset table in msdb to see what was the amount of data actually backed up. Once you identify the databases that are not being used on a daily basis, you can exclude them from nightly backup process. Then I'd go further and determine how often they need to be backed up (the number should be very small based on the result of your steps above) by talking to the business owners of that data.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Jul 2008
    Posts
    1

    SQLsafe can do all of this

    Since the guy already has SQLsafe and it's setup to run backups for his environment, just set SQLsafe to do differential backups, like rdjabarov suggested, and then use SQLsafe to give you info about the size of backup, amount of objects backed up, etc. Then you can make your decisions about for what and how often backups should be run against each db.

    Also, if you want to exclude objects from the backups that you know never get changed, just exclude those objects in SQLsafe for that db's backup definition. I'm pretty sure you can do this in both the backup wizard and the policy wizard.

  5. #5
    Join Date
    Jul 2008
    Posts
    2

    Thank You

    Thanks guys. I'll discuss these options with him and get back to you in case he needs any more info.

    Thanks Again!

  6. #6
    Join Date
    Aug 2008
    Posts
    1
    I think what the dba is looking to accomplish is a way to detect if any objects within the db were accessed on a particular day. If there was no activty in the database then he would be able to skip the backup of the database entirely that day leaving the previous days backup file in place until the next day. I know some guys who use Idera because of the compression features, and the fact that the backups/restores usually run faster than native sql.

Posting Permissions

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