Post reply

Backup strategy suggestions

  • February 12, 2008 at 2:58 pm

    #119975

    I am working on devising a plan to backup all of our SQL servers in our network. I currently have about 10 servers that I need to have create a plan for and have a few questions.

    I checked with my backup guy and our policy is to due 'differentials' Monday through Friday and Fulls on Saturday.

    Part of my plan was to include basic integrity checks, statistics updating, reindex, defrag tables.

    I should mention, most of my servers are 2005 with a few 2000.

    My question is, I was going to setup a maintenance plan to fire off after hours.

    One maintenance plan I was creating had everything I listed above, and did differentials at night. Since most of my databases are in FULL recovery mode, what is the best way to schedule my transaction logs?

    I determined that I would need to do transaction log backups every 4 hours, Monday-Saturday.

    Any suggestions on building that plan, and ensuring the get on the backups that are run nightly?

    Little fuzzy here and was hoping to get pushed in the right direction.

    Much obliged.

    Jason

  • February 13, 2008 at 1:40 am

    #777706

    Think you have a nice and efficient maintenance plan on your mind.Go ahead.All the best.:D

  • February 13, 2008 at 8:55 am

    #777894

    I would suggest doing other integrity, optimization taks in the week end rather than doin that every day. So that you never out your server under constant pressure.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • February 13, 2008 at 9:20 am

    #777909

    Following on from the previuos post, you could save your index rebuilds until the weekend, but if index fragmentation is a problem then you could rebuild online during the week (at quiet times) or defrag. This can be done without disrupting your end users

    Gethyn Elliswww.gethynellis.com

  • February 13, 2008 at 4:40 pm

    #778150

    Thanks for the input. I appreciate.

    I created two maintenance plans.

    One runs nightly for backups.

    The second one runs once a week, on Sunday, and it does the updating of statistics, defrags tables, organizes indexes then backups.

    Now, im trying to coordinate a backing up of the t-logs in here some where.

    Any suggestions there?

    Thanks.

  • February 13, 2008 at 10:38 pm

    #778201

    Hi

    do nightly backups take only the differential backups. So u have only one differential backup a day ?

    Log backup and Differential backup strategies are best decided by the amount of data thats being manipulated and the data loss that u can endure. Also factor in the time and resource that the server would consume to take these backups.

    "Keep Trying"

  • February 14, 2008 at 4:46 am

    #778277

    working on the same right now I am going to set up both in a test system and see how big each data volume is what time it takes etc. Trying Log-shipping too.

    Actually I was hoping for a RAID1 for logfiles, but no option here. That would have solved everything, no more thinking about differential or logfile backups.

    But may be you can do ... thats probably best.

  • February 14, 2008 at 5:30 am

    #778285

    For T-log check the size of your log backup file for 4 hrs. if it's big than increase the frequency of T-log backup to 1 hr.

    Now the advantages of 30 min or 1 hr T-log is the minimum dataloss. If something goes wrong and unable to take T-log backup with norecovery option than you can loose data upto 30 min or 1 hr only.

    Moreover if your database activity is during office hrs only than you can think about the omitting t-log after the office hrs.

    So, all depends on how important is your data.

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • February 15, 2008 at 9:14 am

    #778889

    Ask a couple of more questions from the network guys. Are they doing SQL backups or file backups of your backups? Just because they do differentials of the files during the week, does not mean you have to do it that way.

    I'm lazy, and want as easy as recovery as possible when something goes wrong. Therefore I prefer to do full backups nightly, when possible. I have some databases that where too large to do this with, but with SQL LiteSpeed I can run full backups of my entire main production server every night, and still get them done before the differential backup to tape happens. Less to worry about in case of an emergency is allways good IMO, i.e. less files that need to be restored.

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

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