Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 192
  • Last Modified:

Database Backup Tasks

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.
  • 4
  • 3
1 Solution
lcohanDatabase AnalystCommented:
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.
niaidsdtAuthor Commented:
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?
niaidsdtAuthor Commented:
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.  
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

lcohanDatabase AnalystCommented:
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:
lcohanDatabase AnalystCommented:
"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.
niaidsdtAuthor Commented:
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, Epol....so 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.  
niaidsdtAuthor Commented:
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.  


Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now