April 1, 2008 at 7:52 am
On maintenance plan, there is a check box on integrity tab page, is it a good practise to do that first? if there is error found, will the backup stop? thanks
April 1, 2008 at 9:19 am
Yes you should do it and if there are errors, you need to be alerted. most of the time errors are hardware problems and you need to fix them.
I don't think it stops the backup, but the backup may or may not be useable.
April 2, 2008 at 3:52 am
You need to do inegrity checks, but there are pros and cons for including them in the backup process. You need to decide what is best for your site.
Pros...
1) If there is a problem with the integrity check the backup wil not work. There is no point in taking a backup of a corrupt database.
2) It can simplify maintenance processes to do an integrity check immediately before a backup.
Cons...
A) You increase the time required to take the backup. This can cause scheduling issues at some sites, where it is more convenient to schedule these tasks in separate windows.
At my old place, scheduling issues caused us to take backups before the overnight batch work started, and run integrity checks after it finished.
If an integrity check fails, regardless of if it is done at the time of backup or done separately, the recovery process is much the same. Recover from the last known good backup, apply log updates to the time of corruption, then re-do any missing work.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
April 2, 2008 at 8:35 am
The follow ups are absolutely correct. As Ed stated, in depends on your site, but I'll add, also you application(s). At my previous employer, our payroll app had a much larger outage window. I could do all my maintenance task (DBCCs, index rebuilds, whatever) prior to my backup. If corruption or user screwup occurred after this, a simple restore (backups and/or log) and off we go. Our manufacturing app had a much smaller window so my backups occurred nightly (logs more frequently) but my other maintenance plans only weekly. If we had a reason to restore, I had to restore and then potentially rebuild indexes, checkdb or whatever before I would/could turn it back over to the users. Be aware of what you running and be sure it fits each application at your site.
-- You can't be late until you show up.
April 2, 2008 at 2:09 pm
Be aware that selecting the 'do checks for backups' option also apllies to the trans logs; this can lead to situations where your log file backups take excessive time and I/O due to a complete check being done on the DB before the log is backed up.
Karl
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply