Database Backup Tasks

Posted on 2011-10-24
Last Modified: 2012-06-27
I"m fairly new to SQL administration.  Wanted to hear what some best practices are for backups.  Currently we have a nightly maintenance plan that runs and does a full backup.  Our databases run under simple recovery model.  

While we can capture full's every night, we are not doing anything else like "re-index or re-build" indexes.  Looking for some info on createing a nightly maintenance plan that will do the index reorganizing/rebuilding/shrinking all in one.  What are the pros to doing the re-organizing and rebuilding nightly and what are the cons.  

Also interestd in seeing what others do with their datbases to prevent them from growing and becoming fragmented.
Question by:niaidsdt
    LVL 39

    Expert Comment

    1. never shrink you db other than maybe t-log file if it grows unexpected - this is because it will most likely always grow
    2. your backup/restore and impl;icit the recovery model full vs simple depends on on your business rules that should drive your disaster recovery planning. A fair comon plan would be to have at least a full daily backup as you have but if you can't afford that much data loss and need a point in time restore than you need to switch to full recovery mode and do lets say each 4 hours t-log backups.
    3. you should bakup your T-logs at least daily to keep your transaction log files under control.
    4. you should have at least once a week if not daily a reindex/refresh stats job against all your DB's and I suggest using REBUILD which is an ONLINE operation. I tried reorganize but never got even close to the rebuild performance boost therefore we are not using that.

    Author Comment

    In what order are maintenance tasks ran.  I'm thinking of doing the following on all databases (user and system)

    Check Datbase > Rebiuld Index > update Statistics > Shrink Database > Backup Database.

    I've seen other saying that
    Check Database > Shrink> Reorganize > Reindex > Update Statistics

    Why would you shrink before you rebuild the indexes?

    Author Comment

    Also i've read that Rebuilding INdex's can be done online only if you run Datacenter, Enteprise, and Developer version of SQL.  We are running Standard.  

    What is the net effect of not keeping the indexes online?  I've tried to simulate how an applicaiton behaves when a backup is ran with "Keep INdex online while reindexing" but the job never completes.  If i Uncheck the "keep index online...." the job finishes just fine.  I just tacked it up to not running one of the special version of SQL.  
    LVL 39

    Accepted Solution

    Here's what I would do:

    Check database - I assume you mean DBCC CHECKDB but that may be time consuming and depends on which "checks" and how are done. Maybe once a week and not part of the daily maintenance.

    REBUILD indexes with ONLINE = ON where fragmentation greater than 5 for tables 10+ million rows or 10 for tables less than 10 million rows. I personaly found that REORGANIZE does nothing and DBCC INDEXDEFRAG may be more expensive than REBUILD so I do only REBUILD.

    REINDEX?? - no - you do a REBUILD is enough in my opinion

    UPDATE STATISTICS table_name for all tables where you did the REBUILD or at the DB level as SP_UPDATESTATS - more at link below for similar topic

    SHRINK DB? - NO as 99% if not 100% of the time the user DB's need to grow. Only thing to shrink if grows uncontrolled is the T-log after you did the backup.

    BACKUP - FULL at least once a day and T-log after that at least once. If needed by busines you can back it up more often for PITR restore:
    LVL 39

    Expert Comment

    "What is the net effect of not keeping the indexes online?" - ouch...the table index where index REBUILDs will be offline or unavailable and maybe locked. Not good news for a 24*7 business...
    If your business is not 24*7 and/or have weekends when this can be done than do at least following daily:

    SP_UPDATESTATS on each DB, FULL BACKUP, T-log backup/shrink

    and weekly idealy on weekends

    DBCC CHECKDB, REBUILD where needed due to fragmentation, SP_UPDATESTATS on each DB, FULL BACKUP, T-log backup/shrink for a weekly package.

    Author Comment

    The applicaitons that run off or these databases are not 24/7.  To my knowledge there has never been any maintenance preformed on them...aka the only maintenance tasks that are run relate to Backing up and cleaning up backup history so, yes, we have a significatn number of tables grather than 90% fragmented.  

    We are applications that run off these databses are not 24/7.  They are used to run behind the scene applications such as Vcenter, if the databases are offline or slow for a period of time that's not of great concern.  I suspect as we do have lots of fragmentation and lots of white space within our databases, the first time we run the rebuild and update statistic tasks it would take sometime.  To mitigate this i may schedule an outage of the application, move the database to a non production server and preform the maintenance task on there as to not burdon the production server.  Once all the databases have had their  index rebuilt and the databases are now attached to theproduction database, idealy i'd like to setup 1 maintenance plan and set it and forget it (see attached).

    The attached would run daily.  there would also be one that is ran weekly to clean up the history.  

    Author Comment

    I found this free ebook

    It has provided me everything i need to know to get things rolling as well as why you should do some tasks and why you should not do others.  


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Suggested Solutions

    Title # Comments Views Activity
    index rebuild failed 8 76
    Parameterised queries 4 54
    SQL Select - Finding chars in a column 2 39
    SQL Server 2008 9 30
    Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
    In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now