Solved

Recovered SQL database not accessable

Posted on 2012-03-29
12
452 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
ID: 37781223
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
ID: 37781505
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
ID: 37781556
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
ID: 37781569
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
ID: 37781627
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
ID: 37781682
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 12

Author Comment

by:DLeaver
ID: 37781821
@ 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
ID: 37781978
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
ID: 37782408
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
ID: 37783527
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
ID: 37783769
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
ID: 37784031
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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
Viewers will learn how the fundamental information of how to create a table.

920 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

15 Experts available now in Live!

Get 1:1 Help Now