Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Backup and Housekeeping with Maintenance Plans Expand / Collapse
Author
Message
Posted Thursday, January 27, 2011 3:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 19, 2013 9:13 AM
Points: 36, Visits: 134
You may run into trouble but surely it would be better to have something to try and restore (corrupt or not) rather than nothing?
Post #1054454
Posted Thursday, January 27, 2011 3:44 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:12 AM
Points: 1,030, Visits: 2,796
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 Ellis

gethynellis.com
Post #1054459
Posted Thursday, January 27, 2011 4:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 19, 2013 9:13 AM
Points: 36, Visits: 134
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
:)



Post #1054477
Posted Thursday, January 27, 2011 6:26 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:12 AM
Points: 1,030, Visits: 2,796
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 Ellis

gethynellis.com
Post #1054518
Posted Thursday, January 27, 2011 7:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 19, 2013 9:13 AM
Points: 36, Visits: 134
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!
Post #1054557
Posted Thursday, January 27, 2011 7:23 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:12 AM
Points: 1,030, Visits: 2,796
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 Ellis

gethynellis.com
Post #1054568
Posted Thursday, January 27, 2011 7:30 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 11:56 AM
Points: 880, Visits: 2,435
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."
Post #1054573
Posted Thursday, January 27, 2011 7:50 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 5:36 PM
Points: 1,786, Visits: 1,116
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,
Post #1054587
Posted Thursday, January 27, 2011 12:24 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:12 AM
Points: 1,030, Visits: 2,796
Hi Abi, I'm pleased you enjoyed the article.

Gethyn Ellis

gethynellis.com
Post #1054838
Posted Thursday, January 27, 2011 1:14 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 8, 2014 4:10 PM
Points: 21, Visits: 103
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.



Post #1054884
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse