THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

When Your Hard Drive Comes Knocking... A Cautionary Tale

Imagine yourself, happily computing (or whatever it is that you do with your computer). It's a fine, sunny day, narry a cloud in the sky, and you're happily typing along when all of a sudden you hear a rather alien sound eminating from your hard drive. Something that sounds, perhaps, like some combination of a roofer banging in a nail, and a miner's pick as he works on releasing a stubborn piece of ore from a cave wall. 

Certainly not a good sound to hear coming from the general region of your hard drive on a nice, sunny day. Especially when you have not taken a backup in over two years...

Now consider your options. You could...

  1. Turn off your computer immediately, Google "data recovery", and call one of the multitude of companies that pops up. They will charge you in the neighborhood of $500 to get your data safely off of your damaged disk.
  2. Immediately stop working, grab an external USB drive, and transfer all of your valuable data onto the external drive. Order a new internal hard disk, and keep working, confident in the knowledge that your disk is going to die soon, but you've already salvaged the valuable data and a new disk is on the way.
  3. Try to get clever. Think to yourself "maybe if I run 'chkdsk' it will go through the bad areas of the disk, mark the sectors as unusable, and I can keep using this disk."

Such was the scene at my desk last Friday. And you have probably already figured out where this is going. Yes, I ran 'chkdsk'. The first three (of five) checks completed without error, and I thought everything was going to be fine. Then, during the fourth phase, after about an hour of intense clicking and banging noises, a message appeared on the screen, which I can only paraphrase at this point:

Not enough space is available on the disk to fix the bad sectors.

This was a concerning message, given that the disk was much less than half full.  But only a minute or two later my concerns were answered by another rather vague message:

Unspecified error has occurred. Aborting.

An unspecified error during a 'chkdsk' run is never a good thing. And so I rebooted, only to discover that my hard disk was no longer recognized by my system as a hard disk. I did what I should have done in the first place -- called a couple of the friendly data recovery companies -- and after listening to my story and, nicely enough, not laughing at me outright, they told me that my chances of data recovery were near zero percent. You see, when your disk is banging away like that, it's the sound of the heads hitting the platters. And they're not supposed to do that. When I ran 'chkdsk', I forced the heads to touch every surface of the platters, thereby scratching them into oblivion.

A quick trip to my neighborhood computer store, a new hard drive, and I'm more or less back in business, minus two years worth of documents, most of which I never bothered to back up. A bad way to start the weekend, but I actually can't think of anything especially valuable or irreplacable that was lost.  This is more of an extreme annoyance.

So please, learn from my mistake, and next time you hear an odd clicking sound, don't try to outsmart your already-broken hardware. Listen to your computer. It's sending you a very clear (albiet perhaps Morse-coded) message. And back up your data now and again! I work hard to tell my customers about all of the great reasons they should back up their enterprise data; but like most people, I never think about applying those same behaviors to my personal machines.

As an aside, when I reinstalled Vista on the new drive I accidentally used the 32-bit rather than 64-bit media. After I realized my error I decided to stay on the 32-bit version for now and see how it performs. I was not happy with memory consumption before, and I suspect that the 32-bit version will be a bit leaner. Since I only have 2 GB of RAM in this machine, there is no great reason for me to run 64-bit anyway. I'll post again in a while about my thoughts on 32-bit vs. 64-bit Vista once I've had a chance to work with it a bit more intensely.

Published Wednesday, May 28, 2008 7:02 PM by Adam Machanic

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



John Paul Cook said:

My neighbor uses Gmail and writing is part of her job. None of her content is confidential. I had her open a Hotmail account and create a folder named Backup. A filter rule was created to send any email with a subject line of "backup" to the Backup folder. Whenever she creates a document worth saving, she emails it from Gmail to Hotmail. That means she actually has two backups of every important file, one in Gmail Sent Items, and another in Hotmail.

May 28, 2008 6:47 PM

Adam Machanic said:

Hi John Paul,

I do the same thing with a lot of documents, in my Gmail account.  Works quite well!  And since Gmail automatically saves all of the e-mails I send, I have a record of every document I've sent out over the past 3+ years.  But for every one of those documents there are a few more that I didn't think to back up... Painful!

May 28, 2008 7:16 PM

KKline said:

Ugh!  That's just awful!  

That happened to me a few years ago and although I didn't lose any important work data, I lost a ton of important family pictures that I'd downloaded to my laptop.  I still have the hard disk in case I scrap together an extra $500 to get the disk rebuilt.

Are all of your presentations still safe?  Even the new one(s) for TechEd?


May 28, 2008 7:51 PM

Denis Gobo said:

There is always spinrite from Steve Gibson :-)

May 28, 2008 8:00 PM

Paul Nielsen said:

Hey Adam, Good thing you blogged or published your best writing!


May 29, 2008 12:02 AM

TiborKaraszi said:

Ouch... Whenever you hear that noise, you get this feeling that ... something important is going to happen.

I wrote my own backup sw a fre years ago, after two disk crashes in a row. The app cnnects to a SQL Server read where and what to backup and then connects to my NAS to create a new folder for the backup. Since I'm only about 200 MB or so (the documents to backup, that is) this work great. And I wipe everything which is more than 5 days old, expect for folder created 1st day of month. Only downside is that I have to separate large stuff to its own folder, but since large stuff tend to be more static this isn't a big deal...

May 29, 2008 6:19 AM

Scott Whigham said:

Adam -

I'll suggest how to automate this so you don't even have to think about it. You need a few things:

(1) a command-line ZIP tool, (2) batch files, and (3) Scheduled Tasks in Windows.

1) I use WinZip's command line tool

2)  A batch file looks something like the code at the end

3) Schedule the task to execute the batch file nightly. You also have the option of executing it manually at any time.

Here's the basic idea of my batch file. Mine is actually much larger and includes all of the folders that I want to ensure that I have backed up:


rem This takes a backup of several key folders and stores it in a folder titled with todays date/time

@Echo off

Set yyyy=%DATE:~10,4%

Set mm=%Date:~4,2%

Set dd=%Date:~7,2%

Set DateStamp=%yyyy%%mm%%dd%

IF "%TIME:~0,1%"==" " (SET hh=0%TIME:~1,1%) ELSE (SET hh=%TIME:~0,2%)

Set min=%TIME:~3,2%

Set sec=%TIME:~6,2%

Set TimeStamp=%hh%%min%%sec%

Set backupFolder=d:\Backups\LearnItFirst Websites and Databases\LIF Backups - %DateStamp%_%TimeStamp%

Set remotedir=\\Duke\shared\Backups\LearnItFirst Websites and Databases\LIF Backups - %DateStamp%_%TimeStamp%

Set localFolder=nothing

Set zipFileName=nothing

Set localFile=nothing

Set localfile=nothing

rem Prep-work: Create the folder to store all backups in

md "%backupFolder%"

If ErrorLevel 1 Goto ErrMsg

md "%remotedir%"

If ErrorLevel 1 Goto ErrMsg

rem Step 1: SSIS Packages

Set localFolder=d:\My Companies\ - Business Admin\Integration Services Projects

Set zipFileName=SSIS

Set localfile="%backupFolder%\"

"c:\program files\winzip\wzzip.exe" %localfile% "%localFolder%\*.*" -p -r -u

If ErrorLevel 1 Goto ErrMsg

xcopy %localfile% "%remotedir%\"

If ErrorLevel 1 Echo Unable to copy file to external backup: "%backupFolder%\"

rem Step 2: OneNote

Set localFolder=d:\OneNote

Set zipFileName=OneNote

Set localfile="%backupFolder%\"

"c:\program files\winzip\wzzip.exe" %localfile% "%localFolder%\*.*" -p -r -u

If ErrorLevel 1 Goto ErrMsg

xcopy %localfile% "%remotedir%\"

If ErrorLevel 1 Echo Unable to copy file to external backup: "%backupFolder%\"

Goto Exit





Hope this helps!

May 29, 2008 6:46 AM

jerryhung said:

Ouch, I would've

1. shut down desktop

2. find an external HDD

3. turn on desktop, start backup right away

4. when it reaches that "bad sector", it'll probably die either way, but at least now I have everything BEFORE that point

Now my backup plan is

** - best Sync/Backup FREEWARE ever **

1. Use SyncBack to sync my desktop to File Server (software RAID5 in Windows 2000) nightly

2. Use SyncBack to weekly backup from File Server to external HDD

Note: I can't bother to backup onto DVD b/c I have ~1TB of stuff

May 29, 2008 10:58 AM

Denis Gobo said:

Adam check out JungleDisk

I heard some good things about it, it stores your data on Amazon S3

They have pricing info on the site and it is cheaper than a Drive (you can always write it off anyway)

May 29, 2008 11:06 AM

Jonathan Kehayias said:

Thanks to you and Kimberly Tripp, I won't be posting my own blog message about a crashed drive and no backups. WhenDidYouLastBackupYourHomepersonallesscriticalSystemIsItReallyLesscritical.aspx

She has gone through more drives this year than I think I have in the last 10.  I hope that your luck is not about to follow hers.

May 29, 2008 4:07 PM

Saggj Neumann said:

Hey Adam,

You're in good company with Kalen, Kimberly and Paul all blogging about personal HD disasters...

I highly recommend using microsoft's FolderShare ( to automatically synchronize your important folders between a bunch of computers. I don't really like the whole "backup in the cloud" idea... It hasn't earned my trust yet. However, Synchronizing my files between my laptop and two desktops (in different locations) means I can work on any of the computers, and the online backup is a really nice bonus...


S. Neumann

May 29, 2008 4:29 PM

Saggi Neumann said:

My mistake - Kalen didn't blog about HD disasters (knock on wood...) :)

May 30, 2008 1:27 AM

andyleonard said:

Hi Adam,

  I am sorry to hear about your old HD (may it rest in peace).

  I colleague recommended Carbonite ( a few months back and I installed it. I was tempting fate with a laptop that was over a year old.

  I opted for the 2-year plan at $90 USD. All plans allow unlimited storage and retrieval so far as I know. I'm storing about 30 G out there. The documentation states it is all encrypted (twice, I believe).

  It took a couple days (literally) to remotely backup the directories I specified.

  One nice thing is the client runs in the system tray and automatically updates anything I change / add / delete from the marked folders.

  Another nice thing is it was very easy to get all the information back on my new laptop without really thinking about it. I installed the client, logged in, was prompted to migrate my subscription from my old machine to the new, and allowed it to do its thing for a day or so. All marked directories were downloaded to my new machine automagically. Pretty cool in itself, absolutely necessary for DR, and not too expensive (imo).

:{> Andy

May 30, 2008 9:31 AM

Adam Machanic said:

Hi all,

Thanks for the great suggestions!  I will certainly take some into account (Carbonite being one of the top contenders, since I'm lazy and would rather let them handle it <g>).  It's probably a good lesson for me in practicing what I preach.  Now I can tell my customers that I actually know the -pain- they will suffer if they don't back up!

May 30, 2008 9:38 AM

Kalen Delaney said:

Saggi.... I read that and thought that maybe you knew something I didn't!

I do backups of critical stuff every week.. and my current writing is written to a USB drive or Passport, whatever is handy, at least twice a day.

I have also done the 'email to myself' trick when I needed to make sure some file was safe, and didn't have anything external to copy to.


May 30, 2008 11:57 AM

Patrick Jackman said:

Sorry for your troubles Adam. I had a similar experience a couple of months ago.

So I installed an HP Windows Home Server in the attic with 2 500 MB drives. Windows Backup jobs are scheduled every evening at 8:00 PM: daily, weekly and monthly.

Once a month I also use Acronis 9.1 to make an image of my PGP Whole Disk Encrypted drive to a Lacie USB disk.

So I think I'm covered against my next drive crash and any bad guys that break into the house. I don't have a fire protection plan though.

BTW, I really enjoyed reading your book.


June 1, 2008 8:36 PM

Mark Allison said:

Hi Adam,

I have a Linux server at home which I use for backups among other things. There's a great little utility on there called BackupPC which backups up all machines on my home network, but the great thing about it is it compresses the files when they are backed up, and if the file already exists on the backup server it creates a reference to it instead of copying the file again. This way you can get hundreds of gigs of backups in much less space. It's really impressive, and has a web GUI which can be accessed from any machine at home.


July 10, 2008 12:39 PM

Adam Machanic said:

In late April, I posted a puzzle to test readers' knowledge of SQL Server query processing internals

October 1, 2008 3:43 PM

Penlope said:

Oh Adam sorry to hear that.Well with me i prefer having backups online because to me they are safe.This prevents me from losing my files and it is cheaper to me than data recovery.I use

Here i only pay 50bucks per year for space of 200GB and inaddition they also offer a free unlimited 5GB trial version.This is so incredible to me.So incase my hard drive crashes am on a safer side.Now days i backup all my two Computers on one account.

June 12, 2010 4:53 AM

Leave a Comment


About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog


Privacy Statement