mdf file is corrupted in ms sql server 2008 database

[Pedro Soldado]

Hi guys,

My database .mdf file got corrupted and I have no means to get my schema s.I tried with a few soft wares available on the internet but its turns out that all of them being demo versions don't give back the repaired file.

Is there any other means by which I can restore my db?

I am using MS SQL 2008.

Please, any help!

[Erland Sommarskog]

Post by Pedro Soldado
My database .mdf file got corrupted and I have no means to get my schema
s.I tried with a few soft wares available on the internet but its turns
out that all of them being demo versions don't give back the repaired
file.
Is there any other means by which I can restore my db?

If you have a clean backup of the database, restore the database.

If you don't have a backup, your prospects are bleak. Maybe it can be
repaired, maybe it can be hack-attached so that you can salvage as much
as you can, maybe it can be repaired manually. But it is very difficult
to say, since a database can be corrupted in millions of ways. And you need
a good understand of SQL Server internals do something like manual
repair. And plenty of time on your hands.

See this blog post from Paul Randall how to hack-attach a database:
http://www.sqlskills.com/blogs/paul/disaster-recovery-101-hack-attach-a-
damaged-database/

[c***@gmail.com]

There is a manual solution to fix this problem, but you need to have Microsoft SQL Server Management Studio for that. If you have it, you need to follow the steps given below:

Open Microsoft SQL Server Management Studio on your system.
Now click on New Query button.
A new query page will be opened. Write the SQL scripts (shown below) on the page:

EXEC sp_resetstatus [YourDatabase];
ALTER DATABASE [YourDatabase] SET EMERGENCY
DBCC checkdb ([YourDatabase])
ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC DATABASE ([YourDatabase], REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE [YourDatabase] SET MULTI_USER

Now click on Execute.

Now you can see the file is not tagged as Suspect.

Some useful links from Internet space:

http://www.filerepairforum.com/forum/microsoft/microsoft-aa/sql-server/1462-sql-server-2005-database-lost - great solutions and a lot of users who can share their knowledge about sql databases
https://sql.recoverytoolbox.com/ Recovery Toolbox for SQL Server - in case any solution cant assist you, you make use of this tool

[Erland Sommarskog]

Post by c***@gmail.com
There is a manual solution to fix this problem, but you need to have
Microsoft SQL Server Management Studio for that. If you have it, you
Open Microsoft SQL Server Management Studio on your system.
Now click on New Query button.
EXEC sp_resetstatus [YourDatabase];
ALTER DATABASE [YourDatabase] SET EMERGENCY
DBCC checkdb ([YourDatabase])
ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC DATABASE ([YourDatabase], REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE [YourDatabase] SET MULTI_USER
Now click on Execute.

Before you do this, make sure that you take a copy of the cracked MDF file!

This may work out but:
1) Corruption may be such that DBCC waves the white flag.
2) REPAIR_ALLOW_DATA_LOSS means just that, and DBCC could gladly throw all
your data away.

Run the above step by step, and if the first DBCC succeeds (that is,
spews out a long list of corruption), the recommended repair level is
at the bottom.

But best if you have a backup to restore...