Urgent Help to Restore a Database from Loading State

Question

Urgent Help to Restore a Database from Loading State

CDBA

Hall of Fame

Points: 3266
More actions
October 26, 2006 at 1:30 pm

#192887

Some of my team mate tried to copy Database1 to Database2. He got Database2 in fully operational but Database1 is in laceName w:st="on">LoadinglaceName> laceType w:st="on">StatelaceType>. Database1 was using Full recovery model.
Now I got the detached .mdf and .ldf of Database1 and need to bring back Database1 as operational. I tried to attached it but got an error [Error 5173:Cannot associate files with different databases.]. Can any one please help me how to bring back Database1 in operational state?

CDBA

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

Are both databases on the same server and were sp_detach_db and sp_attach_db used to create Database2? If so, you get the error when trying to attach Database1 because you can't use the same physical files for more than one database.

The best way to copy a database on the same server is to restore a backup with a new name. See 'How to restore a database with new name' in BooksOnLine.

Greg

CDBA Hall of Fame Points: 3266 More actions · Answer 2

Hi Greg,

Both DB are on same server. But the issue is like this:

1. Database2 was directly restored from Database1 [Dn't know how exactly he did the restore.. but must be using SQL Server SA right ]

2.Database2 is now online

3. Database1 went to Loading state for ever.

4. In the mean time, the person killed the process which was responsible for the restore. [He told me he killed a process where he found his name.. So I am guesiing he killed some restore process.

Now I wanted to recover Database1. I detached it and tried to reattach it. But it is now throwing the error.

Can u please guide me now? Thanks a ton in advance

Regards

Utsab Chattopadhyay

CDBA

Greg Charles SSC-Forever Points: 45403 More actions · Answer 3

I'm still unclear about how Database1 came to be in a loading state. If a restore was in process, try this in Query Analyzer:

RESTORE DATABASE Database1 WITH RECOVERY

Greg

John Burchel Hall of Fame Points: 3611 More actions · Answer 4

Hi Mate,

I had this problem last month, make sure that you sort out the following:

Both databases have thesame level of service packs, i.e make sure one isnt older than the other and vice versa..
Use the with recovery option when restoring.

Let us know how you get on.

Cheers

Kindest Regards,

John Burchel (Trainee Developer)

CDBA Hall of Fame Points: 3266 More actions · Answer 5

Thanks Greg. Actually it is a mystry for me also how Database1 went in Loading State

And I detached the database now and not able to attach it now. So I restored Database1 from a good backup. But I am not understanding how this could happen? Also we found that Database2 was not fully copied [Like some coluns of some tables had NULL].

The developer told me he did the below:

1. Tried to restore Databse2 from Database1 directly

2. Database2 came online but Databsase1 got in Loading state

3. So he killed the processes initiated by him

4. Still Database1 was in Loading state and he escalated to me.

Issue is now resolved as I got a good backof of database1. But yet to understand what exactly happended? Any idea?

Many thanks in advance

Regards

Utsab Chattopadhyay

CDBA

CDBA Hall of Fame Points: 3266 More actions · Answer 6

Hi John

Thanks a lot. It happened on a same box only. Now I am trying to figure out the root cause a I wrote to Greg above. Any help from you on what may be the cause?

Thanks in advance.

Regards

Utsab Chattopadhyay

CDBA

Greg Charles SSC-Forever Points: 45403 More actions · Answer 7

Hi Utsab,

What does the developer mean when he says he "tried to restore Database2 from Database1 directly"? Maybe it's the teminology that's confusing me. I think of "restore" as meaning restoring from a backup file.

Greg

CDBA Hall of Fame Points: 3266 More actions · Answer 8

Hi Greg,

Even I also not getting the same thing!!! As per my understanding we can restore a DB from a backup only. But he is not able to reproduce the scenerio. He told that he did direct copy using SQL Server Wizard. Any idea if it is possible to copy a DB from another DB directly? The only info I got he used SQL 2005 Express installation tools to acess SQL 2000 using SA right

Thanks for the help.

Regards

Utsab Chattopadhyay

CDBA

Greg Charles SSC-Forever Points: 45403 More actions · Answer 9

Hi Utsab,

Well, I'm stumped. If he used the Copy Database Wizard, he should have gotten an error for the destination database because, as I recall, you can't rename the database during the copy. Since he was copying to the same SQL Server instance, he couldn't have two databases with the same name.

I'm glad you got the original database back on line. Maybe you should think about removing the developer from the Sysadmin role!

Greg

CDBA Hall of Fame Points: 3266 More actions · Answer 10

Thanks Greg!! I am also confused on what he actually did… So implemented the restrictions already on him.

Thanks again for your continuous helps..

Regards

Utsab Chattopadhyay

CDBA