Post reply

Backup frequency

  • July 12, 2004 at 11:43 am

    #83432

    We got a database  and recovery model is full, update from 6:00 AM to 6:00 PM (Monday to Friday, sometimes Saturday). Cuurently we take full backup and transaction log backup at night.

    Is this enough to recover when server fails?.

    If not what type of stratigies you recomend?. Please help me on this.

    Thanks.

     

  • July 12, 2004 at 12:21 pm

    #514295

    Depending on how much data can you afford to loss, You may run transaction log backup more frequetly.

  • July 12, 2004 at 12:58 pm

    #514301

    Thanks for your quick reply. I heard that, Point in time recovery is possible, how that works.

    And what type of failures can we expect?.

     

  • July 13, 2004 at 10:19 am

    #514422

    srgangu, as the other post mentioned, backup frequency is a product of your willingness to lose data.

    If you have a very active system then you need to determine just how much data loss is acceptable.

    For instance, in my shop, our systems are very active 20 hours a day, I did some measurements on when the activity occurred and monitored how much activity took place. After intense discussions with the CTO we came to the decision that 10 minutes of data loss would be acceptable. Based on that, we do a complete backup at night, backup the transaction logs every 10 minutes and at the mid-point of the activity window we do a differential backup.

    You need to run tests on your backups to see if they will restore correctly and to determine just how long it will take to recover fully from a complete failure.

    Hope this helps

    Gary

  • July 14, 2004 at 2:23 pm

    #514552

    Thanks for your response. Our database is active 6:00 AM to 6:00 PM Monday to Friday some times on Saturday. My manager accepted to lose 15 min data. Our database size is around 500MB. Would you please suggest me how can i implement this. And what are failures can happen?.I think asking too much questions.

    Thanks Again.

  • July 14, 2004 at 2:59 pm

    #514557

    As to what can happen -

    The server fries its

    • motherboard
    • two drives in a raid set *
    • the raid controlller *
    • the NIC
        .

        Others include:

        • The trainer for the company logs the new users into the production database and says enter garbage. +
        • The end of day process multiplies all your order quantities by random numbers. +
        • The end user deletes some of the records out of the "local" copy of the data she has in an Access DB. +
        • The server crashes and deletes NTOSKrnl.exe, databse.mdf, and sqlserver.exe during chkdsk because it says they are corrupt.
        • The city water department cuts the power to your building on Jan 2. See server crashes above
        • Your consultant pulls out a floppy that has a virus on it. +
        • Your senior NT tech merges the Exchange server registry onto your SQL Server
        • Your building burns at 2:00 AM and the nightly backups were still running *

      I'm sure these are so uncommon none of them will happen to anyone.

      To do a point in time (PIT) recovery you would need to set up a maint plan that does, preferably, a nightly full backup. And then just do a Trans log backups every 15 minutes. This is assuming it is just dumped to disk and retained for 3 days. The idea being that the backup of the server on a nightly basis will pick up the full and trans logs backups as files off the disk.

      Then practice restoring the database as another name.

      The PIT recovery in normal day-to-day is usually acceptable off the disk. But you have to consider the possible levels of disaster to what ends up being reasonable levels of recovery and at what cost. 😕 The ones I put an asterisk (*) by above are probably going to put you back to the last backup on tape unless you do log/file shipping offsite. :^) The ones with a plus (+) sign sort of depend on when the error was detected. If the deleted records weren't noticed for 3 hours you may be able to restore the database as some other name, and extract the records back. But if the EOD problem isn't caught for three hours you may have to go back to the prior night.

      Just throwing out my $0.02.



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.

  • July 14, 2004 at 5:28 pm

    #514569

    Thank you very much. Which one is the best to take backups (logs, data)

    Maintenance plan or t-sql

    What are the advantages and disadvantges?.

     

  • July 15, 2004 at 7:35 am

    #514650

    The maintenance plan wizard simply builds the T-SQL jobs and schedules them. Go to the %Server% -> Database Maintenance Plans and build one. Then go look under Management - SQL Server Agent -> Jobs and you'll se them. If you open it and go to the "Steps" tab and edit the step(s) you can pick that up and run it in the query analyzer.

    Also note that a suggested method is to do separate maint plans for the system databases apart from your user databases. You can't do trans log backups on some of the system databases, a bug exists that won't allow integrity checks on some system databases. And really you only need to back them up after configuration changes. I do mine daily anyway.



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply