recover an SQL .mdf file that is currently written to a bad sector

[questorfla]

That is as best as i can explain it.  The company who wrote the software is notorious for being UNhelpful.  They connected and told me the problem we had were due to a "xcorruption" of the Logs file so nothng to worry about.  He fixed that and left paying NO attention to the Other Windows error logs I was trying to indicate that said Urgent Warning Bad Sector replace drive.
Anyway, his proof was that their program was working again.  AND it also starting making its own backups again (It had stopped which is why i called).  BUT  their backups are proprietary and i cannot recover from them or even see what is in them.

If the data is STILL in a bad section of the hard-drive and Windows is still telling me to get it out/fix it/replace the drive etc and i ignore those warnings it would be my fault if something ever happened. (or that is how I see it)

I have tried every tool i have (which isn't a lot) and I can copy every file in the DATA folder in that SQL instance, except the one that matters the most.  The  .mdf file which is 2,315,904KB and it cannot be copied due to a cyclic redundancy error.
I was able to get it copied by zipping it and copying the zip, but when unzipped I  am left with 2,181,760KB file size  which I presume means something got left out.  The DB is so large that if left where it is, it will continue to grow but the backups will all be bad.
While their tech may be comfortable with leaving it on a system with Windows screaming to replace the drive, I am not so warm and fuzzy about that.

[Shades]

While I'm not a fan...try if you can use CHKDSK, in the off chance Windows is actually able to repair the bad sector(s) or capable of moving data around to spare sectors.

Then I would try software such as TestDisk to retrieve data. At least it doesn't give up that quickly as Windows explorer when copying files. UnstoppableCopier is another piece of software that won't give up easily when copying files. Both are free to use.

If you don't trust the hard disk to last much longer, you might be better off to clone the current hard disk to a new (similar storage capacity or bigger) hard disk, disconnect the current hard disk to keep the original data as safe as possible and try to retrieve data from the cloned hard disk. This way is likely best, as you will need a new hard disk for your server anyway. And with cloning you have a 1-on-1 copy, so the server should work immediately after the cloning is finished and the original HD is disconnected. Especially when you can get a new hard disk that is the same brand and model as the original.

If you have more than one SQL Server running, you could dump the original database and import it in the other SQL Server. That would be fastest and give you an option to check for content and/or structural differences between both databases and make fixes where necessary. Which is also not hard to do with the software that comes with the server software. As your database is only 2.3 GByte in size you can download the free SQL Server 2012 Express software, with which you are allowed to run a database smaller than 10GByte. There might be already an express edition from SQL Server 2014 available, if you fancy the latest.

And as a general rule: always verify if your dumps are correct...don't trust the message saying the dump was created successfully, but upload it again and actually check for errors during import and do random checks for structural and/or content integrity. I have been burned by dumping Oracle databases that show no error in the dump log file, but still are capable of generating import errors. This hardly ever happens, so if you feel lucky, go without the verification...but don't say you weren't warned.

Making backups/dumps from a MS SQL Server database is quite easy and fast. At least it is on my MS SQL 2012 Server with the management software that comes standard with the server software. PowerShell is very handy too when making SQL database dumps.

[4wd]

While I'm not a fan...try if you can use CHKDSK, in the off chance Windows is actually able to repair the bad sector(s) or capable of moving data around to spare sectors.

Then I would try software such as TestDisk to retrieve data. At least it doesn't give up that quickly as Windows explorer when copying files. UnstoppableCopier is another piece of software that won't give up easily when copying files. Both are free to use.

Would it be a good idea to make a sector clone of the HDD before using a recovery program?

[Shades]

Further in my comment you will find the same statement...which applies when questforfla is of the opinion that the hard disk won't survive too long anymore.

On the other hand, there is not much harm in using CHKDSK if the hard disk performs as expected, except for one or more sectors. That was the impression I got after reading his first post, so I mentioned that first. Then again, I might have missed something, as English isn't my native language.

[4wd]

Further in my comment you will find the same statement...which applies when questforfla is of the opinion that the hard disk won't survive too long anymore.

Yes, sorry, should have made it clearer.  I meant make the clone regardless of what recovery software ends up being used - looking back over his posts I was wondering whether this is a new fault or something that's been building up over the last few months, eg.

[2015-03-30] https://www.donationcoder.com/forum/index.php?topic=40549.msg378792#msg378792

I have a need to temporarily use something other than the DB's internal backup of a SQL Express DB.  It normally runs its own backup  every night and i keep copies of what it backs up.   Unfortunately, it is having issues of some kind and until they can resolve them I would like to be able to keep some kind of backup even if it isn't the one they normally keep.

[Shades]

@4wd:
You are correct in that cloning is the most secure way to rescue your data. However, from questforfla's previous posts I gathered that the company he works for doesn't like to spent money on IT. With that in mind I made my comment.

You could also be right about this problem being the cause of earlier issues with their backup procedures. I missed that previous thread while lurking here. 

Anyway, questforfla should be thinking about running multiple SQL Express servers. One that is used as production server and another that synchronizes with the production server on regular intervals. When the production server is having a problem, the redundant server can take it's place, everyone can continue and he has time to properly fix whatever is the problem of the production server.

The main problem with such a setup is the license from the software they use for their business. Does their license tolerate the use of a redundant server or not.

[bob99]

I haven't used this yet but from what I have read and heard, it may be able to move the data to a good sector so it can be read. Others here may have direct experience.
It isn't a free solution but if the data is valuable the price may be insignificant.
SpinRite. https://www.grc.com/sr/spinrite.htm

[questorfla]

Maybe it was me that did not make this clear enough.  Cloning would be a Perfect Solution and I DID buy the Server Edition of Macrium at over $200 to do just that.
BUT
it will not run.
It cannot Read the drive to clone it so no good there.  Cyclic Redundancy Error.  I tried several passes and never get any further.
I need a Good Clone or there is no point.  I have also tried copyng as much of the file as can be red and simply attaching the db to a completely new insall of the software.  That also fails.

When the Program that is on the drive runs and it can see all the currently entered data, it acts like there is no problem.  I have no idea how this could be other than it is a crappy program.  Windows is throwing Bad Block errors at me one per minute or more often.
The company who wrote the software is NO help.  Their program SAYS it makes a backup.  But only They can read it, they do not tell anyone else how to see what is in it.

I think i have done my duty nd am ready to give it up.  I warned them all, I did everything i could.
MAYBE one day if they ever NEED one of those backups and then find out that none of them has been any good for the past 10 months or so, maybe then i will be vindicated but I am honestly tired of trying to help when no one here understands the problem and no one at the "software company" cares about it.

Thanks for your input, as always.  In my eart, i knew that there wasn't going to be a good solution for this but it never huts to ask.

[Shades]

HDClone - that would be a tool to use. The freeware version is slow, but it does the job. It will create a bootable device (CD/DVD/Pen drive) and after you connect both hard disks, you only need to boot that system up and it will clone your hard disk. Might take a bit (no pun intended) on the problematic sector(s). It doesn't use Windows at all and that is a good thing in this kind of cases.

There are alternatives to HDClone if you so desire, but I can (and will) personally attest to the excellent qualities of HDClone.

Maybe Macrium is good(enough) at cloning, but as far as I know their software to be able to make images of a hard disk. And Google confirms.

An image is not a clone...it's close, but it isn't a clone.

[Stoic Joker]

Okay, this bit is marginally insane...but the sensible options appear to have been exhausted.. It seems that the db is attesting to be both valid and corrupt at the same time. I say this once before with a db that hadn't been properly maintained for a few years, and had some of it's slack space on a bad sector.

Probably best to hear from Shades before trying this, but...

If you run the MSSQL maintenance routine (Shrink database/files) on the db to compact the db it may allow you to get the actual data "out" while leaving the (presumed) corrupt slack space where it is.

It's a dangerous, and quite possibly insane idea. But if the current instance of SQL is indeed the only thing that can read the db, then maybe it can be leveraged (e.g. tricked into) copying the db for you.

Shades??

[Shades]

@Stoic Joker:
It is indeed a dangerous proposition. If the database remains working, moving data around by shrinking may be the step that saved your database..or it will create more severe problems. I would try that last.

Now I must say that I am not too accustomed wit SQL Server, Oracle is what I know. But the lessons learned in my encounters with that fickle beast apply to SQL Server as well.

@questforfla:
To create a full dump from your database, it shouldn't take much more than:

Code: (sql)

USE <insert database name>;
GO
BACKUP DATABASE <insert database name>
TO DISK = 'Z:\Bak\SQLServer\<insert database name>.bak'
    WITH FORMAT,
      MEDIANAME = 'SQLServerBackup',
      NAME = 'Full Backup of <insert database name>';
GO

Creating another database server with SQL Server Express on a different computer should be easy. The defaults provided by the installer are sufficient for a database like yours. You will encounter problems restoring a database on the same server, so make sure you use a different server on a different computer!

Uploading the dump you created to the new server is also not difficult:

Code: (sql)

RESTORE DATABASE [NewDatabaseName]
    FROM  DISK = N'Z:\Bak\SQLServer\<insert database name>.bak'
    WITH  FILE = 1,  NOUNLOAD,  STATS = 10

This can also be done with the SQL Server Management Suite (SSMS for short), an option enabled by default in the installer. It was in the SQL Server 2012 software I used to setup my server, and that is the only SQL Server I have experience with. Even if the old DB server doesn't have it installed, you can install it on the new server and use SSMS to connect to the old server. If you know the passwords for the old server, you will be amazed how easy it is to export the old database and import it into the new database. SSMS is a very nice tool and easier to work with than what Oracle delivers with their server software. I can tell you that much.

SSMS (for SQL Server 2012 at least) comes with functionality to compare databases. The Oracle software also comes with such functionality and that software doesn't care if the compared databases are not on the same db server. I assume it is the same with SSMS. Even if that is not the case, there are 3rd party tools or scripts that will.

You could try the TOAD for SQL Server server (available in free/commercial versions). TOAD is much more powerful than the Oracle software and the TOAD for SQL Server software should be in the same league.

Dumping and restoring your database is the first option I would try. Mainly because that is the easiest (especially if there is a redundant SQL Server running in your environment).

Cloning the hard disk from the old server is the second option. But assuming that this server remained active during this thread, it should be first option by now.

After that I would try shrinking the database and more or less hope if that works out.

Next time your company negotiates for a new license from the company that delivers the software you work with, get the most angry person that works there and make him/her curse them to hell about holding your data hostage, especially in cases with imminent hardware failure. And seriously look for other vendors of similar software, and let the current vendors know your company is doing so.

[questorfla]

Thanks, everyone for the input.  i thought i would let you now what happened in the end.
I finally was able to get the software company to take oe of the backups of the data, and let me restore it to the GOOD clone i had that was three months old.  Since it was their software that was failing to make the daily backups as it should and apparently could only be forced to make a good one when they connected, I was able to make a point that I could not be responsible for their failures.  They had to admit that their automatic backup was not not functioning but that was about it.

All is well that ends working so i am happy to be back up (we did lose a days work though) because he pulled off this trick before the close of day so all the files that had not yet been added from their temporary storage never made it to the backup he ran.

Thanks for all the advice, I at least ended up with some decent cloning software for that server now and I am still going to make a monthly copy of the complete drive to be extra safe