SQL Server Performance Forum – Threads Archive
Opinions: dbcc or backups first?
Generally it is believed that you should dbcc your databases BEFORE backing them up, so you can be sure the backups are "clean". I am now not so sure… and I am switching my servers to backing up first. One of my live servers has had hardware problems (memory banks & some of the memory had to be replaced). This cropped up during overnight maintenance. The dbcc check was running, and crashed the server (due to the HW problem).. this meant the dbs were not backed up, because dbcc checks were the first step in the overnight maintenance. Obviously this is an unusual hardware problem (but memory errors caused on-disk corruption in one of the dbs! – requiring a restore, which therefore had to be from the night before), but it has made me re-think my strategy for maintenance. My question is, what do you do first? And would you rather have a backup first, or would you rather have a clean database first, then a backup? Tom PullenDBA, Oxfam GB
I prefer to backup then perform maintenance ready for the next day. Id rather have a backup of a unoptimised database so if corruption was to occur, at least I can repeatedly reproduce the steps taken afterwards, in order to get to the bottom of it.
Nothing more frustrating to me than only being able to see the symptoms of a problem, and not know the cause
On 3 of the services we do a backup first and then DBCC checks, till now we never had any issues with this approach. On 1 of the service we had hardware issues soon after DBCC checks and while BACKUP was running, we could get it sorted out by restoring the same database and rerun DBCC checks again. _________
Satya SKJ
When I used to teach Microsoft SQL Server classes, Microsoft’s recommendation in the class was to run DBCC command both before and after backups. This seems like overkill to me. What I do now is to run DBCC commands only once a week, and full backups each night. So far, I have yet to see any database errors found by DBCC commands when running SQL Server 2000. Perhaps I am a little too overconfident. ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
They certainly are rare in SQL Server 2000. In fact I’ve never had one that wasn’t hardware-related (unlike in 7.0). This one server was bad because the memory problems caused on-disk corruption. My recommendations:-
If one of your servers does something weird like spontaneously reboots without any errors in any SQL Server or Windows error logs, immediately run a full dbcc checkdb(), to ensure corruption hasn’t been caused permanently.
Also ensure you have hardware vendor tools (such as Compaq Insight Agents or HP Top Tools) to proactively alert you to hardware problems. Tom Pullen
DBA, Oxfam GB
Funniest thing is on one of the service the cluprit was COMPAQ insight agent. Somehow it fixed with other drivers from Compaq. _________
Satya SKJ
One environment I worked in got bitten by that they only did dbcc integrity checks twice a month. A hardware error in the disk array occured that corrupted the database more and more for almost two weeks (no monitoring tools from the hardware supplier detected this, they had to run some low level checks later on). Just prior to the next dbcc check users of the system had noticed more and more that some parts of the application didn’t work. These errors could not be fixed by the dbcc repair commands without data loss which was not an option. This meant that we were stuck with almost 2 weeks of database backups that were usless. None of the full backups created during the last two weeks could be used since they all contained corrupt data. We had to go back a bit more than two weeks to find a full backup that wasn’t corrupt. Then we restored it on another system and applied all transaction logs till the last one, thus re-applying the transactions on this healthy system. But this took a long long time since it was quite a big database and we had to grab all transaction log backups from tape. If this hadn’t worked, we would have lost 2 weeks of data. Repairing with dataloss as mentioned wasn’t an option. So because of this experience I always do dbcc integrity checks before each backup (if possible). Note that I’m only talking about integrity checks here. Optimization like rebuilding indexes etc is done maybe once a week but it depends on the application/system. /Argyle
]]>