Attaching .mdf file with database changes mode of database?

Asked By nadeemrao 0 points N/A Posted on - 08/09/2011

I have attached the database in SQL Server 2005 using .mdf file. But as soon as it was attached, database went to suspected mode. I tried a set of statements to recover it to normal state but after running those queries, its status changed to emergency mode.

Can you please tell me that how can I recover my database from emergency mode to normal?

 

• Status: Open
• Question Views: 1158
• Answer Count: 2
• Vote Up 0 Vote Down

• Answer Accepted: Yes
• Question Category: MS SQL Server

Best Answer by Paul Mac

Go To Solution

Answered By dainy 0 points N/A #92744

Attaching .mdf file with database changes mode of database?

I had the same issue and I tried a lot to recover my database. But, unfortunately, there is no way. The database gets locked in emergency mode for any kind of modification and it is left only with read option forever. Apparently, there is no way to get the database back to normal from emergency mode.

What you can do is, copy all data from this database to new database. Here are the simple four steps you can take to copy your data from database with emergency mode to normal mode database:

• Create new database
• Create DTS package
• Use Copy SQL Objects task to transfer data from old database to new database
• Execute the DTS job and you are done!

About dainy

Questions
0

Answers
5

Best Answers
3

• Vote Up 0 Vote Down

• Posted on - 08/19/2011
• Question Category: MS SQL Server

Best Answer

Best Answer

Answered By Paul Mac 0 points N/A #92745

Attaching .mdf file with database changes mode of database?

Suspect mode:

A database can go into suspect mode when the system is improperly shutdown of database server and corruption of the database files.

When you are in suspect mode your database server won’t allow you to perform any operations on the database until the database is repaired.

Emergency mode:

This mode is used when the transaction log for the database has been damaged and no backups are available to restore from.

When the system is in emergency mode the following step are followed:

1. Force recovery to run on the transaction log.

This is essentially recovery with CONTINUE_AFTER_ERROR in a similar vein to using CONTINUE_AFTER_ERROR with either BACK UP or RESTORE.

This recovery with CONTINUE_AFTER_ERROR functionality is possible only from within DBCC CHECKDB.

2. Rebuild the transaction log if it is corrupt.

3. Run the full set of consistency checks on the database with the REPAIR_ALLOW_DATA_LOSS option.

4. Bring the database online.

About Paul Mac

Questions
0

Answers
42

Best Answers
26

• Vote Up 0 Vote Down

• Posted on - 01/06/2012
• Question Category: MS SQL Server