Solved

Recovered SQL database not accessable

Posted on 2012-03-29
12
451 Views
Last Modified: 2012-06-22
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
0
Comment
Question by:DLeaver
  • 6
  • 5
12 Comments
 
LVL 9

Expert Comment

by:rajeevnandanmishra
Comment Utility
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
 
LVL 12

Author Comment

by:DLeaver
Comment Utility
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
 
LVL 9

Accepted Solution

by:
rajeevnandanmishra earned 500 total points
Comment Utility
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
 
LVL 12

Author Comment

by:DLeaver
Comment Utility
Ok thanks

where do I run this - from a command line?

"Run DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS should be run"
0
 
LVL 25

Expert Comment

by:jogos
Comment Utility
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
 
LVL 9

Expert Comment

by:rajeevnandanmishra
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 12

Author Comment

by:DLeaver
Comment Utility
@ 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
 
LVL 9

Expert Comment

by:rajeevnandanmishra
Comment Utility
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
 
LVL 12

Author Comment

by:DLeaver
Comment Utility
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
 
LVL 9

Expert Comment

by:rajeevnandanmishra
Comment Utility
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
 
LVL 12

Author Comment

by:DLeaver
Comment Utility
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
 
LVL 9

Expert Comment

by:rajeevnandanmishra
Comment Utility
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

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

772 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

10 Experts available now in Live!

Get 1:1 Help Now