Solved

Corrupted Mssql Database

Posted on 2014-02-25
22
390 Views
Last Modified: 2014-03-03
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.
0
Comment
Question by:rflorencio
  • 7
  • 5
  • 3
  • +5
22 Comments
 
LVL 9

Expert Comment

by:MattSQL
ID: 39887704
What is the error you get returned from CHECKDB?
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39887738
If you are sure that the database needs recovering, have you got recent backups that you can restore?
0
 
LVL 75

Expert Comment

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

But first make a backup.
0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 39887892
As the first responder said, I would ask what error does CHECKDB return?  Do you have a backup that you can restore?
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39889950
Try one thing: put the database offline and then put it back online see if that changes anything.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39890197
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
 

Author Comment

by:rflorencio
ID: 39891292
Hi,

The backup as the same problem.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39891531
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
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39891537
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
 
LVL 35

Expert Comment

by:David Todd
ID: 39893336
Hi,

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

Regards
  David
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39893347
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:rflorencio
ID: 39894364
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
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39894538
Ok, you should try a SQL service restart and let us know what is the status after that.
0
 

Author Comment

by:rflorencio
ID: 39894603
Hi,

Had already done so.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39894683
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
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39894705
"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
 

Author Comment

by:rflorencio
ID: 39894807
I have a lot of space available, and i have .mdf and log.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39894878
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
 
LVL 38

Expert Comment

by:Jim P.
ID: 39894927
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
 

Author Comment

by:rflorencio
ID: 39895393
Hi,
This database has dozens of tables, I have to find another solution.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39895479
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
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 39896088
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

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now