Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Lumbago
Norsk Yak Master
3271 Posts |
 Posted - 2009-09-24 : 04:18:38
|
My fellow yaks;I was just thinking that I might have a flaw in my backup/integrity strategy that I wanted to ask you about. I have scheduled a DBCC CHECKDB of our primary databases once every week on Saturdays. I also do full backups every night and translog backups to file every 15 mins. However, due to the size of the backup files they are only kept for 2 days. Now what if my weekly dbcc checkdb detects an error of some sort that wasn't noticed during the week and none of my backups are usable?? What do you guys do to prevent this from happening? Keeping >7 days of backups just wont work and doing the checkdb takes too long to be done on a daily basis  - Lumbago |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-09-24 : 04:43:36
|
| Since you're on SQL 2005, make sure that the DB has checksum page verification on, that every single page has a checksum (it will if the DB was created with checksum on, if upgraded then a rebuild of every single index will put checksums on the vast majority of pages) and do the backups with the CheckSum option. That way, the page checksums are checked during a backup and the backup will fail if any of the page checksums are wrong (which is an indication that something is corrupt somewhere)Backup with checksum doesn't do everything that checkDB does, but reading and checking all the checksums will pick up the majority of corruptions.Best case would be to increase the retention period of the backups (or switch to a full/diff/log backup strategy that takes less space), but if that's not an option the backup with checksum is better than nothing.--Gail ShawSQL Server MVP |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-09-25 : 05:16:36
|
| Oh, bonkers! I'm actually on 2008 Std, posted in the wrong forum then. Does it make a difference though? I did add WITH CHECKSUM, STOP_ON_ERROR to my full backups yesterday but the backup didn't take any longer at all so I assume that the pages don't have checksums then? I do a full rebuild of the indexes every week also using Taras isp_ALTER_INDEX store procedure, are the checksums added automatically then?- Lumbagohttp://xkcd.com/327/ |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-09-25 : 05:35:27
|
| Sorry for being a bit slow...this checksum stuff is all new to me. But I just found in the properties of my database that the Page_verify-setting was set to torn page detection. Can I just change that to checksum on the fly?- Lumbagohttp://xkcd.com/327/ |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-09-25 : 10:23:29
|
| Checksums and backup with checksum was introduced in SQL 2005. 2008 has them both.The backups shouldn't take significantly longer if the checksums are been checked. Might take a bit longer, but not double time or anything close. You can switch to checksum at any time. It doesn't do anything intensive when switched on. Checksums will go on a page when the page is modified, not when the option is changed.Tara's alter index script only rebuilds the indexes that need rebuilding, so it could not touch some for months. If you've switched checksum on and want the majority of pages to get their checksum, do a rebuild of all indexes (maint plan will do that for you). Just as a once off operation.--Gail ShawSQL Server MVP |
|
|
|
|
|
|
|
|
DBCC CHECKDB and backup strategy