SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backup and Housekeeping with Maintenance Plans


Backup and Housekeeping with Maintenance Plans

Author
Message
Taggs
Taggs
SSC-Enthusiastic
SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)

Group: General Forum Members
Points: 192 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?
Gethyn Ellis
Gethyn Ellis
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2809 Visits: 2905
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 Ellisgethynellis.com
Taggs
Taggs
SSC-Enthusiastic
SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)

Group: General Forum Members
Points: 192 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
Smile
Gethyn Ellis
Gethyn Ellis
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2809 Visits: 2905
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 Ellisgethynellis.com
Taggs
Taggs
SSC-Enthusiastic
SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)

Group: General Forum Members
Points: 192 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!
Gethyn Ellis
Gethyn Ellis
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2809 Visits: 2905
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 Ellisgethynellis.com
Nadrek
Nadrek
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4600 Visits: 2741
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."
Abi Chapagai
Abi Chapagai
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1928 Visits: 1127
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,
Gethyn Ellis
Gethyn Ellis
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2809 Visits: 2905
Hi Abi, I'm pleased you enjoyed the article.

Gethyn Ellisgethynellis.com
bcb
bcb
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 107
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. Smile (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.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search