Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

A friend of mine has run into this problem on SQL Server 2008, please suggest a solution:

He came to know that auto backup scheduled on server were not taking place since last couple of days, when he tried manual backup of our database it gave following error:

System.Data.SqlClient.SQLError: Read "D:\DB\data.mdf" failed: 1 (Incorrect function.) (Microsft.SqlServer.Smo)

To safeguard our data he thought of rebooting the server in safe mode & copy the .mdf & .ldf files manually to a safer location but it gave an error after completing 80% of process (MS DoS invalid ...) which looked as if hard disk has some issue & it's not able to read the files from specific location.

He thought of copying the data table by table after creating a fresh database from DB script, during the process one of our developer by mistake dropped two important tables & he has not used BEGIN TRANSACTION option also

Now he is looking forward for an expert advise if we have some recovery tool to recover those tables & data in those tables.

share|improve this question
    
DDL is not possible to rollback until you use BEGIN TRANSACTION. You have to recover these tables from old backups. –  JP Chauhan Feb 20 at 9:49

4 Answers 4

To Copy MDF file of a particular DB, you have to detach first that DB from SQL Server or Stop SQL Server service and then Copy.

share|improve this answer
    
This would not work as the OP says that it gave an error after completing 80% of process (MS DoS invalid ...) which looked as if hard disk has some issue & it's not able to read the files from specific location. –  Kin Feb 20 at 14:23

I would run DBCC CheckDB and see what corruption you have in the database. It sounds like it is disk corruption. If that is the case you will likely have to restore from backup.

After you restore from backup you can compare the two tables in question.

You can also try BCP'ing the bad table out but if it is a i/o disk error it will likely die if the corruption is in the heap.

share|improve this answer

Have you been doing any log backups? If so, restore the database to another server in recovering state and restore log backups to a point in time.

share|improve this answer

If the tables were dropped, ApexSQL Recover can recover them even from databases in the simple recovery model. ApexSQL Recover can recover both table structure and table records

On the other hand, ApexSQL Log cannot recover records lost when a table was dropped, it can only recover the table structure

In case the records that were lost using DELETE (not DROP TABLE), both ApexSQL Log and ApexSQL Recover can help

The advantages of ApexSQL tools over recovery to a point in time is that ApexSQL will recover just the tables you specify (creates CREATE TABLE and INSERT INTO scripts) , while a point-in-time recovery will roll back all the transactions that happened in the meantime

A DROP TABLE statement marks the MDF file pages used by the dropped table for deallocation. These pages are actually still in the MDF file until overwritten by new operations. To prevent new operations overwrite the data necessary for successful recovery, we recommend creating a copy of the original MDF and LDF files immediately

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.