How do I fix a corrupted database file

We have a legal program that uses SQL express 2005. Somehow the company is saying our database has become corrupted and needs to be sent to a company to be rebuilt or fixed. Is there a cheap or free software out there that I can use to fix it?
raffie613Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

warddhoogheCommented:
depends what kind of damage, if indexes are corrupt, you can rebuild those yourself.
0
raffie613Author Commented:
well they didn't tell me what the issue was, it just won't attach when trying to attach it in Management studio.
0
Cenjoy100Commented:
Can you please let me know following things.

1.Did you sent them DB
2.If you have sent or the got it from some mail was it in zip format.
3.while taking earlier DB back up / creating back up file for the db was there any error.
Or 4. did some body renamed any db file which they are trying to attach.

I asked this because this kind of issue may come if we perform task mentioned above.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

raffie613Author Commented:
1.yes
.2.no it was on a flash drive
3. no errors.
4. no

The database sits on an external hard drive. We had to reload windows on the machine that it sits on. when we tried to reattach the database afterwards, we couldn't. The company that makes the software says it is corrupted and gave me the name of a company that would be able to repair it. Just was wondering if there was a tool to download that I could do myself.
0
Anthony PerkinsCommented:
>>Is there a cheap or free software out there that I can use to fix it? <<
Yes.  It is a command that is built in with MS SQL Server and called RESTORE as in:
RESTORE (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms186858.aspx
0
raffie613Author Commented:
Does that work with SQL express as well?
0
raffie613Author Commented:
ok, that seems to be using restore from a backup. we do not have a working backup.
0
Anthony PerkinsCommented:
>>we do not have a working backup.<<
I am sorry.
0
raffie613Author Commented:
Any other ideas on how to repair it? I know there is a third party company that does it.
0
jogosCommented:
>>we do not have a working backup.<<
I am sorry. Indeed

"We had to reload windows on the machine that it sits on. when we tried to reattach the database afterwards, we couldn't."
Any more detail on the attach error?
Could it be that the sql version installed is older as the previous installed?
0
jogosCommented:
And user has permission to attach database?
0
Scott PletcherSenior DBACommented:
So you have only the .mdf and .ldf files?

The only thing left to try is:

1) take a copy of the ORIGINAL .mdf file (it's far better if it's the file as it looked BEFORE you attempted to attach it -- the attach attempt itself will modify the file)
2) if the db has only the one primary fg, attempt to do a "sp_attach_single_file_db" on the file.
If it has multiple fgs, primary and one or more secondaries, you will have to use CREATE DATABASE ... WITH ATTACH_REBUILD_LOG

For example, select "New Query" from withn Express, then issue this command:

EXEC sp_attach_single_file_db 'Db_Attach_Test',
    'x:\path\to\primary\data\file\primarydatafile.mdf'
0
Scott PletcherSenior DBACommented:
With any luck at all, SQL will be able to create you a new log and you're back in business!

If the attach / CREATE DATABASE WITH ATTACH_... works, naturally the first thing you should do is take a full backup of the db before doing anything else to it.
0
raffie613Author Commented:
ok, I think I see the issue. The screen where i go to attach the database has a place to click ADD then I chose the .MDF file. Then it populated in the top where it says database files.
HOWEVER, on the botton part it says Associated files. Those are point to a non existing drive. How do I chage that?

Here is the error.

TITLE: Microsoft SQL Server Management Studio Express
------------------------------

Attach database failed for Server 'LIBBYNEW-PC\SQLEXPRESS'.  (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.5000.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

------------------------------

Directory lookup for the file "e:\CPSHARE\CPWIN\SQLDATA\LIBBY.MDF" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft SQL Server, Error: 5133)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.5000&EvtSrc=MSSQLServer&EvtID=5133&LinkId=20476


0
raffie613Author Commented:
Also, Which SQL is version 628? One database that was a backup is telling me it can't atach because I am running version 612 and the database is for version 628.
Thanks.
0
jogosCommented:
Versions ar internal (undocument) numbers for patches

SQL Server 2005 databases have version number 611/612
SQL Server 2008 databases have version number 655
So you have to apply a SP to be able to restore.

The file location is the one it finds in the mdf file, you can/have to change it yourself to where it is located.
0
raffie613Author Commented:
How do I know which service pack I need? I thought I had SP 4 for sql 2005 express.
it reads version9.00.5.00

it is not allowing me to change the file location on the associated files. Hopw can it have a completly different directory that the database I am attaching?
0
warddhoogheCommented:
9.00.5000 = mssql 2005 with SP4
0
raffie613Author Commented:
yeh, so that is what i have. Still saying the versions don't match. Does that mean the DB is corrupt?
0
warddhoogheCommented:
not necessarily, you could install a 2nd instance of MSSQL2005 from your installation CD, which will have no SP normally. try restoring on that instance and if no match, just install the next SP on it, until it works.
0
raffie613Author Commented:
but it keeps saying it can't be downgraded and I have the highest service pack available for SQL express 2005.
0
raffie613Author Commented:
TITLE: Microsoft SQL Server Management Studio Express
------------------------------

Attach database failed for Server 'LIBBYNEW-PC\SQLEXPRESS'.  (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.5000.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

------------------------------

The database 'LIBBY' cannot be opened because it is version 628. This server supports version 612 and earlier. A downgrade path is not supported.
Could not open new database 'LIBBY'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 948)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=948&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------
0
raffie613Author Commented:
Do I need to install SQL express 2008?
0
Scott PletcherSenior DBACommented:
Good idea, that should do it.  You should be able to "restore up" a version no problem.
0
warddhoogheCommented:
0
raffie613Author Commented:
Think it will matter if I download the 64 or 32 bit version?
0
warddhoogheCommented:
if you are going to install it on a 64bit Operating System, install the 64bit SQL version obviously.
0
raffie613Author Commented:
was just concerned it would mess it up if the database was originally on a 32 bit SQL version
0
Anthony PerkinsCommented:
>>was just concerned it would mess it up if the database was originally on a 32 bit SQL version<<
There is no difference between a database from a 32 and 64-bit server.
0
raffie613Author Commented:
how do i remove just the sql 2005 tools? I keep getting install errors when trying to install management studio for sql 2008.
0
raffie613Author Commented:
Also, I read something that I need a latest service pack. Do I need 2008 R2 or regular SP3?
0
raffie613Author Commented:
ok,
IS there a way to recover a database I accidentally deleted in MAnagement studio?
I meant to remove it, not delete it.
0
jogosCommented:
2008R2 is a real version, not just another SP.

Deleted database  -> that's where backups are for , you don't need to restore it because you didn't want it alive anymore but still wanted to keep the db-files (that is what I understand) so you eventualy take care that the backup file of that database is saved on a location where you don't clean it up together with old backups of the other database.

>>we do not have a working backup.<<
I am sorry.
I hope that was clear enough for you to start with backing up (on another storage as your db-files). If not I'll say it loud and clear. Take a full  backup now, now as in before you do anything else execpt for 2 things : warning your boss you will do that and getting a coffee.

0
raffie613Author Commented:
We still haven't gotten the thing to work yet so was not able to take backups. Was wondering if the database is stored somewhere in SQL program files or something since it didn't send it to my recycle bin.
0
jogosCommented:
Default mssql indeed locates database-files default in \Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data\  look for *.mdf;*.ndf;*.ldf. Default does not mean it is so at your place.

We still haven't gotten the thing to work yet so was not able to take backups.
Which database is it then?  It's not the one you wanted to restore I think.
0
raffie613Author Commented:
ok looks like i found it in there. but when I tried to attach it, i got an error message the database name already exists. Then I click refresh in the database pane on the left and the name is there. But it says object error, unable to expand.
0
raffie613Author Commented:
sorry, when I try to expand the database, it says not accessable.
0
raffie613Author Commented:
does that mean it is corrupt?
0
raffie613Author Commented:
ScottPletcher:
EXEC sp_attach_single_file_db 'Db_Attach_Test',
    'x:\path\to\primary\data\file\primarydatafile.mdf'
 
When I ran this after I was unable to open the database, I get the same error as when I try to attach the database via the GUI.
"database"db_attach_test already exists, try a different name.
0
raffie613Author Commented:
ScottPletcher:

After that I hit refresh over in the explorer pane in the left, and the database seems to be there, but when I try to expand it, I get the unable to access (object) error
0
raffie613Author Commented:
anyone know what this error means?
0
jogosCommented:
The database you accidentily deleted , the one you wanted to recover first and now is inaccessible are that all the same databases?
You have permission to that db?
Look in the errorlog of sqlserver and see what it says.

0
raffie613Author Commented:
I have persmissions for it.
The one I deleted and found and another one that I have both attempted to pull into a 2008 SQL  express management studio, but were originally working in SQL 2005 express, are both giving me the same errors. The name already exists.

All databases that I have not yet tried pull into SQL 2008 are working fine in SQL 2005.

Seems to be some sort of upgrading issue.

Anyone ever heard of that?
0
jogosCommented:
exec sp_helpdb

Open in new window

or
select * from sys.sysdatabases

Open in new window

and check the status column , explanation for the values at http://msdn.microsoft.com/en-us/library/ms179900.aspx
0
jogosCommented:
And I am truly chocked that you tried to install an new sql version without taking a backup first even when   you knew it before.
Even before the next coffee, see that you have at least a copy of your sql files befor something happens with them and you cannot return to an undamaged version.  Once in 2008 there is no return possible to a 2005-engine for that db-file  
0
raffie613Author Commented:
Jogos:
I hear ya, I am cleaning up someone elses mess here.
Thanks for all the help.
What will running these do?

1:
 
exec sp_helpdb

Toggle HighlightingOpen in New WindowSelect All


or

1:
 
select * from sys.sysdatabases
0
raffie613Author Commented:
here is the result from what you told me to run

master      1      0x01      0      65544      1090520064      2003-04-08 09:13:36.390      1900-01-01 00:00:00.000      0      100      C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\master.mdf      661
tempdb      2      0x01      0      65544      1090520064      2012-01-09 10:53:46.257      1900-01-01 00:00:00.000      0      100      C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\tempdb.mdf      661
model      3      0x01      0      65544      1090519040      2003-04-08 09:13:36.390      1900-01-01 00:00:00.000      0      100      C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\model.mdf      661
msdb      4      0x01      0      65544      1627390976      2010-04-02 17:35:08.970      1900-01-01 00:00:00.000      0      100      C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\MSDBData.mdf      661
0
jogosCommented:
Just stressing the importance.

That gives you the list of databases that already exists, even the one that are not accessable yet while they are restoring, offline.... But no user databases.

When you run that statement
EXEC sp_attach_single_file_db 'Db_Attach_Test',
    'x:\path\to\primary\data\file\primarydatafile.mdf'

Open in new window


The locaton x:\path..... is the location of that db-file now and also the old location?  If not the mdf and ldf-file on the original location still exists?
0
raffie613Author Commented:
yes they still exists and yes the path I am pointing to is where the databases are that I want to attach.

Are there known issues for upgrading the databases tfrom sql 2005 to 08?
That is definitly what is going on here.

All other databases that were not attempted to be pulled into SQL 2008 management studio are fine.
0
jogosCommented:
1. I understand that all your other db's are fine now.  But are they in 2008?
2. You started to make a maintenance plan for backup and maintenance of that server. (yes I'm here again but it's important)

3 Problem-db
Are there known issues for upgrading the databases tfrom sql 2005 to 08?
Sorry? You only started to upgrade to 2008 because you had a 'corruption'-issue. And it also didn't want to be attached in your initial 2005.

I understand that you are still stuck on that 'already exists' error.  So do the attach again, try to give db other name and if you try to attach only mdf see that the old ldf is not there anymore.
0
raffie613Author Commented:
Sorry for the misunderstanding. Before we did the 2008 that I mentioned above, the person before me on this, had done it initially. That was why we were getting the different version issue. What caused this I am almost certain was the going from 2005 before we wipped out the machine, to 2008 right afterwards. The "name already exists" was right from the get go.
0
raffie613Author Commented:
Anyone?
0
jogosCommented:
'Sorry for the misunderstanding. Before we did the 2008 that I mentioned above, the person before me on this, had done it initially. '
Pretty important info if you knew what damaged your db,  why not sharing it when asking the question.
Anyone?
I think this could be because there is no response on last suggestion
"I understand that you are still stuck on that 'already exists' error.  So do the attach again, try to give db other name and if you try to attach only mdf see that the old ldf is not there anymore."

And to get me responding again. I will read first that you made and tested a worthy backup-scenario.



0
raffie613Author Commented:
I have tried that. Changing the name only creates the same error message.
0
warddhoogheCommented:
Sorry to say, but this way its going to take forever. Perhaps hire someone locally or someone here? Likely solved in a few hours.
0
raffie613Author Commented:
Well I got a SQL kernal repair software and that did it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
raffie613Author Commented:
Mine was the only correct answer that worked.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.