I was talking to a customer the other day who had an interesting problem: Successful backups. Specifically, their problem had to do with the success messages that SQL backup puts in the SQL errorlog and the system event log. Seems like a nice, friendly thing to do right? Drop a note that your backup...

So what’s a “fuzzy backup” and how does it relate to STOPAT? There has been some confusion about what RESTORE DATABASE WITH STOPAT does, if it works, and why it behaves as it does. This is not about RESTORE LOG WITH STOPAT, but only RESTORE DATABASE. To understand how this works and why, you need...

The session I gave at TechEd this year on 'Secrets of Fast Detection and Recovery from Database Corruptions' was videotaped as part of the Its Showtime! TechEd program. The video is now available to watch at http://www.microsoft.com/emea/itsshowtime/sessionh.aspx?videoid=549 . This is the same session...

So I learned something about SQL Server at TechEd today. In Kimberly and Bob Beauchemin's pre-con, Kimberly mentioned that SQL Server has a 'black-box' trace, similar to an aircraft flight-recorder, which I'd never heard of. It's an internal trace that has the last 5MB of various trace events and it...

First blog post of the year from TechEd! Well, Kimberly and I arrived a day early hoping to chill out in the sun by the pool but contrary to all expectations the weather sucks today – it’s actually much better in Seattle. Instead we're hibernating in our room blogging and having a Blokus re-match – last...

As promised in my earlier post of an example corrupt 2005 database, here's one I've just created for SQL Server 2000. The attached WinZip file contains a backup of a simple 2000 database called 'broken' . It has a simple table called 'brokentable' (c1 int, c2 varchar(7000)) with one row in it. The...

I've been asked several times over the last few weeks for an example corrupt database to play with, and for testing logic built around DBCC CHECKDB. The attached WinZip file contains a backup of a simple 2005 database called 'broken' (I can do a 2000 one too if there's enough demand). It has a simple...

My recent post on SLAs prompted some interest and comments from readers so this is a follow-up to that post. What most people wanted was a list of some SLAs applicable to SQL Server - easier-said-than-done because a lot of SLAs depend on the application being serviced by the database. I had a poke...

Funny how a bunch of people all seem to have the same problem at the same time. Maybe it’s just that people don’t want to talk about corruption until someone else does – it’s like a dark secret that once let out of the bag – everyone talks. The common HA/DR issue over the last few days on the newsgroups...

(In the UK now hanging out with Kimberly and Tony Rogerson before teaching a Masterclass tomorrow in Reading. Then it's off to Copenhagen for SQL Server Open World , with a little R&R in London beforehand and Copenhagen afterwards, before we fly back to the US on Sunday. The weather here is actually...

I was teaching at a Microsoft-internal class last week and there was a discussion on what corruptions can't be repaired using DBCC. At the same time, several threads popped up on forums and newsgroups with people hitting some of this unrepairable corruptions so I thought that would make a good topic...

This is a question I see every so often and it cropped up again this morning so I'll use it as the subject for this week's blog post. There are several ways I could answer this: the unhelpful answer - I've got no idea. the almost-helpful answer - how long did it take to run last time and...

Here's an interesting scenario that cropped up today. You have a database on a RAID array that failed and has zero'd out a page. How can you get the data back? There are two ways to do it, depending on the database recovery model and version of SQL Server - single-page restore or manual insert/select...

(Yippee - just finished my certification dives and got my PADI Open Water certification - just in time for our dive trip to Indonesia in December :-) This is a question that comes up a lot - in fact 3 times this week already - most recently during a guest lecture I did on DBCC for one of Kimberly...

I've come across a few interesting DR scenarios lately, (interesting in the sense that the resolution wasn't immediately obvious, and fortunately the answer didn't start out with "update your resume...") The situation that comes to mind is a customer who was doing all the right things. He had a...

Time to cough up a real-life data recovery scenario to temporarily stop those people who badger me relentlessly for scenarios... We have a situation currently with a customer who managed to delete all their data during a SAN reconfig and then found out that their tape backups hadn't been working properly...

I've been sitting and thinking a lot about guarantees this morning while drinking coffee in the debilitating heat here (85 degrees at 11am) and things get more complicated the more I think about it, so time to stop and post some thoughts. Over the last few days, the question of guarantees has come...

Page checksum is new feature in SQL2005 that provides you a stronger mechanism than torn-page to detect any corruptions in IO path. Here are some details and scenarios PAGE CHECKSUM: Between the time a database page is written to the disk and subsequently read by SQL Server, it may get corrupted...

I'm sure you've been there - something causes corruption in the database. You blame the hardware, the hardware guys blame the software. There's no smoking gun and the hardware diagnostics come back clean. What can you do? SQLIOStress This is the hardware diagnostic you really want to run. It simulates...

Sounds kinda funny, doesn't it? I mean, who here doesn't have someone breathing down your neck every time you go past your backup window? So why would you NOT optimize for fast backups? It just makes sense. You do backups every day, so they should be optimized. Two words: Disaster Recovery If...

Emergency mode repair? In a couple of previous posts I explained how the two worst things you could do to your database are rebuilding your transaction log and running REPAIR_ALLOW_DATA_LOSS . Well, in SQL Server 2005, we combined them into a new documented feature! Are you off your head, I hear you...

In my chalk/talk at TechEd yesterday, I asked the crowd "what's the purpose of repair?" One person got it right. The purpose is not to save data. Surprised? The purpose is to get the database back to a structurally and transactionally consistent state so that processing can safely continue. We chose...

10 points if you answered "don't be daft, never! " and minus several million is you answered anything else. Yes, if you have no backups and your hardware has corrupted your transaction log then you have no choice but to rebuild it, but this should never happen because you all have backup strategies,...

So, I'd like to make a couple of assertions about backup and disaster recovery that will hopefully get you to thinking, and just might change the way you think about the topic. There are two distinct motivations for doing backups, and they imply differing requirements and solutions. OK, what am...

Yes, finally I come clean and tell all. It's an open secret that there's an undocumented DBCC command called DBCC PAGE that you can use to look at the contents of database pages. I've recommended in forum postings that people use it and Product Support also asks customers to use it during various investigations...