ATTENTION: You are viewing a page formatted for mobile devices; to view the full web page, click HERE.

Main Area and Open Discussion > General Software Discussion

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

<< < (3/3)

@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.

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

--- Code: Text ---USE <insert database name>;GOBACKUP 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: Text ---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.

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


[0] Message Index

[*] Previous page

Go to full version