Solved

Corrupted Mssql Database

Posted on 2014-02-25
22
392 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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
 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SSRS 2013 - Overlapping reports 2 19
Sql Server group by 10 23
Whats wrong in this query - Select * from tableA,tableA 11 28
SQL Error - Query 6 24
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…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

815 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

7 Experts available now in Live!

Get 1:1 Help Now