Corrupted Mssql Database

I have a corrupted database, i can make queries but i can´t open tables. Checkdb not work. Someone know any free tool to recover database.
rflorencioAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
ZberteocConnect With a Mentor Commented:
OK, Try the Import/Export wizard. Right click on the target database > Tasks > Import Data > Chosea as Source SQL server > Give connection details > Next > Inoput the destination details > Next > Choose Copy data from one or more tables or views > Next > Select the boxt at the top to select all the tables (Obs, if you will get errors because some of the tables you can come back here and remove that particular table and try agaoin until it will work) > Next > Next > Finish.

As I said, if you get an error you can go back using the Back button and remove the table that caused it and then go next ... again. Repeat until you will remove all the tables that cause problems.

To script out code, procedures, functions and views you can use the Generate Scripts wizard if will work. Right click on the database > Tasks > Generate scripts ... and follow the steps.
0
 
Matt BowlerDB team leadCommented:
What is the error you get returned from CHECKDB?
0
 
Steve WalesSenior Database AdministratorCommented:
If you are sure that the database needs recovering, have you got recent backups that you can restore?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Anthony PerkinsCommented:
If you don't the best you can do is :
DBCC CHECKDB (YourDatabaseName,  REPAIR_ALLOW_DATA_LOSS);

But first make a backup.
0
 
dbaSQLCommented:
As the first responder said, I would ask what error does CHECKDB return?  Do you have a backup that you can restore?
0
 
ZberteocCommented:
Try one thing: put the database offline and then put it back online see if that changes anything.
0
 
David ToddSenior DBACommented:
Hi,

Re the above suggestion: If its really bad, taking off-line will help make things worse.

1.
Is there a suitable backup? Plan to use that.

2.
Look up Paul Randall's blog
http://www.sqlskills.com/blogs/paul/corruption-demo-databases-and-scripts/

Paul's claim to fame is that for several years he 'owned' the SQL storage engine and wrote much of the dbcc checkdb code. He shows how to recover from various corruptions. So he is worth listening too.

3.
Polish your CV.

HTH
  David
0
 
rflorencioAuthor Commented:
Hi,

The backup as the same problem.
0
 
ZberteocCommented:
I beg to differ. It happened many times that a driver failed and the symptom after coming back was similar. Taking the database offline and back online fixed it. Also a SQL service restart fixed it. However if the data is really corrupted will not make any difference.

If you say you can query tables maybe you can try to export them in CSV format one by one and then import them back into another database.

Can you run:

USE database
GO
select * from systables
0
 
ZberteocCommented:
Going further I recommend you to use Ola Hallegrens Maintanence solution, which deals with:

1. Backups
2. Index Optimization
3. Database Integrity Check

Link:
http://ola.hallengren.com/
0
 
David ToddSenior DBACommented:
Hi,

Keep going backwards through your backups 'till you find one that is okay.

Regards
  David
0
 
David ToddSenior DBACommented:
Hi,

I'm curious as to what state the database itself thinks its in.

select name, compatibility_level, state_de3sc
from sys.databases
;

Regards
  David
0
 
rflorencioAuthor Commented:
Hi David,


Result
=====
select name, compatibility_level, state_desc
from sys.databases


AJFB      90           RECOVERY_PENDING


Result of some tries:
=================
RESULT 2
=======
ALTER DATABASE [ajfb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ([ajfb], REPAIR_ALLOW_DATA_LOSS)

Converting database 'AJFB' from version 628 to the current version 706.
Database 'AJFB' running the upgrade step from version 628 to version 629.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 945, Level 14, State 2, Line 2
Database 'AJFB' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
Msg 211, Level 23, State 3, Procedure tr_ftcc_update, Line 1
Possible schema corruption. Run DBCC CHECKCATALOG.

Result 3
======

DBCC CHECKCATALOG ([ajfb])

Msg 945, Level 14, State 2, Line 1
Database 'AJFB' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
0
 
ZberteocCommented:
Ok, you should try a SQL service restart and let us know what is the status after that.
0
 
rflorencioAuthor Commented:
Hi,

Had already done so.
0
 
Jim P.Commented:
I ran into a similar situation a while back. The DB had some torn pages. I was able to create a blank DB with the same structure.

 I then used a series of select statements to copy as much as I could out of the corrupted DB.

I only lost some static data. Maybe you can try that.
0
 
ZberteocCommented:
"Database 'AJFB' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details."

Have you look into that? Maybe is a space issue. Check the SQL Server errorlog.
0
 
rflorencioAuthor Commented:
I have a lot of space available, and i have .mdf and log.
0
 
ZberteocCommented:
Ok. You can try what is described here:

http://www.mssqltips.com/sqlservertip/2871/troubleshooting-and-fixing-sql-server-page-level-corruption/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=20130318

If doesn't help I suggest you to try what I was posting above. Dump data from tables as much as you can and then load them back to a new database.

You could also create a new database on the same server and then simply open a query window and copy the tables like this:

USE NeweDatabase

SELECT * into  NewDatabase.dbo.table1 FROM OldDatabase.dbo.table1
SELECT * into  NewDatabase.dbo.table2 FROM OldDatabase.dbo.table2
...
0
 
Jim P.Commented:
You could also create a new database on the same server and then simply open a query window and copy the tables like this:

If any of the tables have identity columns then you need to name the columns to do the insert statements.

That is why I suggest looking at my post above for building the insert statements.
0
 
rflorencioAuthor Commented:
Hi,
This database has dozens of tables, I have to find another solution.
0
 
Jim P.Commented:
This explains how to script out all the select statements.

http://www.experts-exchange.com/Database/MS-SQL-Server/SQL-Server-2005/Q_26168572.html
0
All Courses

From novice to tech pro — start learning today.