Results 1 to 5 of 5

Thread: Backup routines

  1. #1
    Join Date
    Nov 2003
    Location
    London
    Posts
    169

    Unanswered: Backup routines

    Hi there,

    I am looking for some advice and opinions on daily backup routines on SQL2000 and SQL2005, I want to know what peoples best practices are for nightly full backups. Currently I have the following in place,

    job: daily backup

    step1. truncate log
    step2. shrink log
    step3. backup
    step. updateusage

    job weekly admin

    step1. defrag indexes (sometimes re-index + update stats)
    step2. truncate log
    step3. shrink db
    step4. checkdb


    Does this look good enough?
    is there anything else I should add?
    What do you have in place currently?

    Thanks for your help in advance

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You should do log dumps more frequently to improve recoverability in case of system failure.
    I backup my databases nightly, and do hourly log dumps througout the day. All backups and logs are dumped to disk, and then copied to a network location to guarantee recoverability.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    and you ideally want to move a set of backups offsite and preferably not in the same town\city\suburban sprawl office park just in case of things like hurricanes\flooding\tornados.

    another hint. you do not want to truncate your logs. you want to back them up.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    I am lazy and manage a number of different servers. My priorities are to have a consistent backup strategy from server to server and to ensure that the backup will always be there when I need it.

    For SQL 2000:
    I create two maintenance plans (skipping optimizations, shrinkage and integrity checks).
    One maintenance plan is for all the system databases. I do a full backup on these nightly.
    One maintenance plan is for all user databases. I do a full backup on these nightly and a transaction log backup hourly (in test, only every three hours).

    Depending on the size of the backup, I will backup over the network (we are running 1 GB switched) or local to a dedicated dump disk. Generally, if the aggregate of user databases to be backed up is over 25 GB, then I will back it up locally (we use a SAN, I have a separate job that makes an image copy of the backup disk and runs it to tape once per week).

    I set the retention policy to a value based on business requirements and available storage.

    For SQL 2005:
    I create two maintenance plans.
    One does a full backup on all databases (user and system). This runs nightly.
    The other maintenance plan does a transaction log backup every hour (three hours in test).
    I then edit the maintenance plan to add the retention policy and cleanup activity logs and reports.

    All that being said, when you design your backup policy, you need to understand:

    1. How much data is your business willing to lose (all, some or none)?
    2. How much time is your business willing to spend recovering the database (days, weeks, hours, minutes or seconds)?
    3. How much money is your business willing to spend on backup and recovery?

    These are the principle drivers for your backup strategy. The technology just lets you achieve the requirements that your business sets for you.

    Once you have developed your backup strategy be SURE that you test it. Practice recovery at LEAST every three months. Practice different types of recovery (new build, recover to point in time, recover a full backup, etc).

    Remember too, that there are other things that you need to backup on your database server besides just the databases:
    1. Linked Server settings
    2. DTS Packages
    3. Logins
    4. Jobs

    Regards,

    hmscott
    Have you hugged your backup today?

  5. #5
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by blindman
    You should do log dumps more frequently to improve recoverability in case of system failure.
    I backup my databases nightly, and do hourly log dumps througout the day. All backups and logs are dumped to disk, and then copied to a network location to guarantee recoverability.
    If the DB is going to keep growing to a certain size, I would not shrink it, just for it to get to that size again, it's a wasted effort, plus you have to allocate all those extents again, and that's overhead.

    I usually do

    Full (user and system) - nightly with dbcc checkdb catalog, logins, dts jobs, backup devices, etc.
    Differential - 3X a day
    TLog - Every 1/2 to 1 hr
    nightly idx defrag dbcc indexdefrag.
    weekly reindex. dbcc reindex.

Posting Permissions

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