Post reply

Best practices to backup things up

  • February 5, 2009 at 8:18 am

    #206285

    I'm a Developper forced into becoming a DBA (StillVeryApprenticeLevel). This might be a newbie questions but I have to ask :

    1. Check Integrity

    2. Reorganize Index

    3. Rebuild Index

    4. Update Stats

    5. Backup

    Is it the right order for them to be executed?

    And...

    Is that right : Differential Backup daily and Full Backup weekly?

    Rebuild Index/Stats, weekly?

    Thanks for any piece of advice you guys can give me.

  • February 5, 2009 at 8:22 am

    #938899

    Make sure you also include log file backups

  • February 5, 2009 at 8:46 am

    #938919

    I don't know about order, but you do not need to reorganize and rebuild, do one or the other, and there are scripts available here and other sites for determining what indexes need to be reorganized or rebuilt and there are some out there for determining what stats need to updated as well. I believe that if you reorganize or rebuild stats for that index are updated automatically.

    For backups, the schedule and types of backups you need will depend on the business requirements. A baseline I use is weekly Full, Daily Differential, and Hourly Log backups. In this scenario the most I need to restore is 1 full, 1 Diff and 23 log backups. If you want to improve restore times you probably should do more frequent Full and Differential backup. If you need to really limit potential data loss then more frequent log backups are needed.

    Here's a blog post by Brent Ozar about backup practices he has learned, http://www.brentozar.com/archive/2007/10/backing-up-sql-server-my-own-mediocre-practices/.

    Ignore the mediocre in the link this a good post and Brent is pretty well respected in the industry.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • February 5, 2009 at 1:59 pm

    #939196

    Jack Corbett (2/5/2009)

    I believe that if you reorganize or rebuild stats for that index are updated automatically.

    Rebuild, yes. Reorganise, no.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 5, 2009 at 2:12 pm

    #939206

    I'll go with the differential backup daily and the full backup weekly.

    The transaction log, depending on the database and its purpose can go from hourly to daily.

    Thanks Jack for the interesting blog post by Brent Ozar, I'll create my backups on another server, that'sw way safer.

  • February 6, 2009 at 8:55 am

    #939601

    Do I need to backup the system databases?

  • February 6, 2009 at 9:11 am

    #939613

    Yes. At least full weekly. Particularly master and msdb. master has the information about your user databases, logins, etc... and msdb stores all your maintenance plans, jobs, schedules, alerts, operators, etc...

    One of the most common mistakes by those new to SQL Server is to not backup system databases (tempdb excluded as it is re-created whenever you restart SQL Server).

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

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

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