January 26, 2011 at 1:12 pm
bcb (1/26/2011)
Nice, Gethyn. But i like to run DBCC Checkdb as first step... If checkdb fails, the job quits and you don't end up backing up a corrupt database.
Another good point.
Gethyn Elliswww.gethynellis.com
January 26, 2011 at 1:12 pm
Rahul The Dba (1/26/2011)
nice articleThanks
Thanks Rahul I'm glad you like it.
Gethyn Elliswww.gethynellis.com
January 26, 2011 at 3:28 pm
Good information...
Another point to make, I had to learn at current employer, is timezone on your SQL Servers. I imagine most DBAs deal with servers within their on timezone but we actually have our servers set to UTC since we go across multiple timezones within the US. Something to be aware of if you may do consulting work or something.
I have also heard of guys who setup the maintenance plan but leave the schedule blank. They then use a SQL Agent job and in one of the steps call the maintenance plan when they want it. That way when you are doing a lot of things maybe altering data, you can call that backup maintenance plan more than once to cover yourself. Then also if you have a mix of things you do on your DB that require T-SQL but other things setup easier in maintenance plan, it can give you more flow control within a SQL Agent job. I had never thought about doing it like that when I heard it.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
January 27, 2011 at 2:40 am
Grasshopper :
Surely if your Database is corrupt it is all the more reason to back it up? At least if you have a backup you have something to restore and then try and fix. If the database corrupts further and you don't have a backup to restore you may find yourself in trouble.
January 27, 2011 at 2:54 am
Taggs (1/27/2011)
Grasshopper :Surely if your Database is corrupt it is all the more reason to back it up? At least if you have a backup you have something to restore and then try and fix. If the database corrupts further and you don't have a backup to restore you may find yourself in trouble.
You may run into problems trying to retore a corrupt database. You whould backup your databases regulary and perform checkdb regulary to find any corruption as soon as possible
Gethyn Elliswww.gethynellis.com
January 27, 2011 at 3:15 am
You may run into trouble but surely it would be better to have something to try and restore (corrupt or not) rather than nothing?
January 27, 2011 at 3:44 am
Taggs (1/27/2011)
You may run into trouble but surely it would be better to have something to try and restore (corrupt or not) rather than nothing?
The way to recover a from database corruption, is to restore from a 'good' backup. If your database is corrupt and you backup said corrupt database there is no point in trying to restore from that backup. You still have a corrupt database. Which is way the poster suggested running checkdb before the backup so you know, at least have a good idea, that there is no corruption in the backup.
There are other ways to fix corrupt databases, but that's a little out of scope of this article. If you want to learn more about corrupt databases checkout Paul Randal's blog here http://www.sqlskills.com/BLOGS/PAUL/category/Corruption.aspx Paul used to manage the Storage Engine Team for SQL Server 2005 and 2008 (I believe) and he is the person who wrote DBCC CHECKDB
Gethyn Elliswww.gethynellis.com
January 27, 2011 at 4:49 am
Hi Gethyn,
We run Checkdb within another action plan and if problems are reported we look at the database then.
I think you may be missing the point I am trying to help put across for Grasshopper to think about. Your last good backup may be from last night (in a good senerio) or you may not have one at all if your backup is not running because of a CheckDB detects an error and stops it running.
Consider the two senerios below
Good Senerio
This corruption may be one value in a record that is corrupted and easily fixed (as we have had). If you pick this up just before the next backup you could have lost a days worth of transactions fixing the corruption with a database restore from last night. (may be less if using transaction backups and you can find out when the corruption occured)
Bad Senerio
Your database, due to corruption, may be getting further corrupted until the point where it is unrecoverable. You have no backups because they have not run due to CheckDB stopping the backups running. Where do you restore from? You may only keep a couple of days backup for one reason or another or a low expiry (these are all options open to the DBA depending on his/her requirements).
The point I am making to think about is, if you have a corrupt database that will restore at least you have a fighting chance. If you have a corrupt database you can't get in to, no backup because it was stopped due to CheckDB and all good backups have expired/been overwriten/removed. Where do you go from here?
Al
🙂
January 27, 2011 at 6:26 am
The point I am making to think about is, if you have a corrupt database that will restore at least you have a fighting chance. If you have a corrupt database you can't get in to, no backup because it was stopped due to CheckDB and all good backups have expired/been overwriten/removed. Where do you go from here?
If your corruption is fixable without data loss or restoring then that's fine. Using Grasshopper technique he will have some confidence that his last backup will be good with no corruption in it. There's not alot of point of restoring a corrupt database with a corrupt database backup, assuming you can get it to restore. When you uncover corruption it needs to be dealt with immediately but its not always possible to fix without data loss. Paul's blog and this article from Gail Shaw http://www.sqlservercentral.com/articles/Corruption/65804/ describe perfectly how deal with a corrupt db and how you fix corruption is entirely dependant on the results of Checkdb.
Let's look at it from the other way. You run a backup nightly then run checkdb, checkdb shows up corruption. You follow Gails artilce and it suggests that you restore from a good clean backup, which backup do you use? the one taken just before the checkdb? what if that backup has corruption in it, your restore has fixed nothing and you still have corruption. If then have to restore from the backup from the previous backup, assuming checkdb comlpeted cleanly after it, you can have some confidence that the corruption occured between the running of CHECKDB jobs and you will need to use the older backup. All the while, the time it takes (downtime) to fix is ever increasing.
In truth the answer depends on the circumstance, but there is definately some beneift in using Grasshoppers technique.
Gethyn Elliswww.gethynellis.com
January 27, 2011 at 7:09 am
Sorry Gethin you are totally right.
I was only trying to be helpful by giving another DBA (and others that read these forums) something for consideration. I think it is probably best I keep my opinions to myself in the future if they just turn into an argument which are not helpful for anyone!
Sorry!
January 27, 2011 at 7:23 am
Taggs (1/27/2011)
Sorry Gethin you are totally right.I was only trying to be helpful by giving another DBA (and others that read these forums) something for consideration. I think it is probably best I keep my opinions to myself in the future if they just turn into an argument which are not helpful for anyone!
Sorry!
Sorry I wasn't intending to argue with you, and your opinion is valid and helpful. I was just trying to explain the benfits of the one approach. With most things in SQL Server there is more than one way to do things, I was merely supporting the point that running checkdb before a backup is a good idea. And discussion is the best way to learn and is helpful to alot of people, so please do stay involved and discuss things.
Gethyn Elliswww.gethynellis.com
January 27, 2011 at 7:30 am
Taggs, please do continue to present your point of view; I find that the most valuable part of almost all really useful articles here are the discussions!
In this case, as Gethyn says, there's more than one way, each with benefits and disadvantages.
1) You can stop taking backups after the first sign of corruption; if you immediately notice that, and immediately correct it, and your prior backups are still available, this may well be the best choice.
2) You can continue taking backups even after the first sign of corruption; if you do not immediately notice that, particularly if you keep backups for a long time, you later have more choices on backups to restore. If you don't keep backups for a long time, especially if days or weeks of business transactions have occurred since the first sign of corruption, and the corruption is easy to handle (oh... we don't care _that_ much about that index/table/data anyway!), then you can at least still execute a normal restore for other reasons (that disk that caused corruption completely failed (as did another in the raidgroup), etc.), and deal with the corruption later, rather than tell the business "Well, we can restore... to five weeks ago."
January 27, 2011 at 7:50 am
Gethyn:
Good article, and moreover good discussions. I like the BCB idea of checking database integrity before the backup. I have added Check Database Integrity Task in my daily full database backup plan.
Thanks,
January 27, 2011 at 12:24 pm
Hi Abi, I'm pleased you enjoyed the article.
Gethyn Elliswww.gethynellis.com
January 27, 2011 at 1:14 pm
Well everybody, I'm pleased my comment about starting a db maintenance job with DBCC Checkdb inspired such discussion. Just to add some clarification... I have all of my sql agent jobs set to email me if a job fails. An interesting feature of SQL Server is that if a sql agent job has a checkdb step, and the step completes successfully but checkdb discovers corruption, SQL will still quit the job at that point with failure, and I get my email notification. 🙂 (Now, I assume that is still true... I'm not sure because the last time I had db corruption was ten years ago, I believe in SQL 7.0.) Anyhow, hope that clarifies.
As an aside...I kinda chuckle when SQLServerCentral calls me grasshopper just because I hardly ever comment on articles. I've been a dba for 17 years.
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply