Recovered SQL database not accessable

Firstly I am no SQL expert so please explain any tools or tips fully

A customer of ours was running a database application on their SBS 2003 box before it died completely, I am assuming it was running in SQL 2005.  I have managed to recover the databse files from the server using SSR, howeever when I try and load the database into SQL express 2008 I get the attached error.  I have tried recreating the databse and replacing tha actual DB files in the SQL folder which loads them but I end up with the same error which seems to attached to a dirty shutdown

I recovered the BES database fine from the same server with no issue

Any help or guidance appreciated
SQL-Issue.jpg
LVL 12
DLeaverAsked:
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.

rajeevnandanmishraCommented:
Did you recovered the transaction log file (.LDF) also?

The attached image show that the LDF file is not copied at the same location.
One possibility of error is that the full path is not created on the new server. Please check this.
0
DLeaverAuthor Commented:
Hi

Yes I did however if I try and attach the DB  I get this message back

I may be able to recover a log file from somewhere else but the message indicates more of an issue with the DB
SQL-Issue.jpg
0
rajeevnandanmishraCommented:
It is not the error of DB. It is for log only. But now is a different error.
Try doing below steps:

1. Create database using CREATE DATABASE FOR ATTACH_REBUILD_LOG.

This may fix the problem right away. If database is corrupt then it will not be restored correctly.

2. Run DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS should be run. It will bring back the database operational. This may loose some data but (is the best if there is no data).

In this option as log is rebuilt SQL Server does not use log file to retrieve any data. In this process none of the log operation like transaction rollback will work.

Detail Syntax of the CREATE database can be found on Books Online for further guidance.
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
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.

DLeaverAuthor Commented:
Ok thanks

where do I run this - from a command line?

"Run DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS should be run"
0
jogosCommented:
There is no SQL-backup (.bak)?  

The error you get is typical with taking backups from live databases with some 'file backup' system. The files (.mdf and .ldf) don't match always with a consistant state.

See if on the new environment there is a sql-backup.
0
rajeevnandanmishraCommented:
Hi DLeaver,

You can run the DBCC command from management studio, or from any client that allow you to run the TSQL Statements.
The full command should be:

DBCC CHECKDB (myDatabaseName , REPAIR_ALLOW_DATA_LOSS )

Open in new window

0
DLeaverAuthor Commented:
@ jogos

I recovered these MDF/LDF files from a dead server, I can't find a .bak file but I am still looking - we have literally just been called in to look after this company so we don't know if any backups occured.

@rajeevnandanmishra

I have tired this, not sure if I ran the query in the right place but it came back with this

Msg 233, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)
0
rajeevnandanmishraCommented:
Hello DLeaver,
The error is coming because the connectivity to the server.
From where you have run the DBCC command? I hope you run it from the same window from where you run the create database command?
Was create database command goes succesful?
0
DLeaverAuthor Commented:
I ran the command from the management studio

I created a new database within the management studio, then stopped the SQL service >> deleted the existing DB files for the DB that I just created and replaced them with the ones I took from the server and the restarted the service

If there is a specific command to run then it would be helpful to tell me where to run it from and the syntax required.

I don't use SQL and so this is all a bit of a dark art at the moment
0
rajeevnandanmishraCommented:
Hi DLeaver,

Please open the management studio, and click on the button of "New Query". You can go through Menu options also.
Now copy the below code in the New query window:
CREATE DATABASE myDatabaseName 
    ON ( NAME = myDatabaseName, FILENAME = 'C:\DATA\myDatabaseName.mdf') 
    FOR ATTACH_REBUILD_LOG

Open in new window


In the above query, kindly change the myDatabaseName with your Database Name and C:\DATA\myDatabaseName.mdf with your os file name and path.

Doing this will will create the database and hopefully it should be in working condition. You can check it in the databases tree in the management studio.

If it doesn't allow you to access the database, then remove the current code from the query window and copy/paste below mentioned code in the query window.

DBCC CHECKDB (myDatabaseName , REPAIR_ALLOW_DATA_LOSS )

Open in new window


After doing this, your database should be available. If it is, then first take the backup by "Right Clicking on the Database name and chosing the Backup option".

I hope now, you should be getting your database in working condition.
0
DLeaverAuthor Commented:
Thanks for this, I'll give it a try in the morning

With regards to the second part, how does the database checker know to check the problem database?  shall I add the line to the query as in step one, so it reads like this

1. DBCC CHECKDB (myDatabaseName , REPAIR_ALLOW_DATA_LOSS )
2.                 ON ( NAME = myDatabaseName, FILENAME = 'C:\DATA\myDatabaseName.mdf')
0
rajeevnandanmishraCommented:
Oh dear.
Just run the DBCC command as given by me (after replacing the myDatabaseName with your database name that you have used in step one).

This will hint the checker to work on the correct database.
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
SBS

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.