Unable to attach db

Question

Unable to attach db

jigo0624

SSC Rookie

Points: 48
More actions
August 14, 2008 at 8:27 am

#192482

We have a system where the log file got full. By accident (or not) the log file was deleted. I tried attaching the mdf file but it fails even though a new log file is created when I try ro re-attach it. Does have any suggestion in how I can recover this DB?
J

Viewing 6 posts - 1 through 5 (of 5 total)

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

Paul Randal One Orange Chip Points: 29544 More actions · Answer 1

Although I'm guessing the answer is no, I'll still suggest it - do you have backups you can restore from?

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

jigo0624 (8/14/2008)
We have a system where the log file got full. By accident (or not) the log file was deleted. I tried attaching the mdf file but it fails even though a new log file is created when I try ro re-attach it. Does have any suggestion in how I can recover this DB?
J

What error did it give? Something about "database not shut down cleanly"?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jigo0624 SSC Rookie Points: 48 More actions · Answer 3

Well the general error that I was told was it just said the log was full. When I tried to reattach the mdf file it says log file missing and if I want to create a new one. Even though a new one is created the process fails. When I got to the site, they had Enterprise manager open and the DB was greyed out. After refreshing the screen the DB no longer shows up and that is where we are now.

j

Paul Randal One Orange Chip Points: 29544 More actions · Answer 4

Do you know if the database was shutdown cleanly? i.e. were there any active transactions when the database was shutdown? If not, you should be able to hack it back into the system. If so, you'll still be able to hack it into the system, but you will have inconsistent (and possibly corrupt) data that will have to be dealt with (more on that if we need to).

Using a query window, what is the output from:

use master

go

select status from sysdatabases where name ='yourdbname'

go

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Marimuthu-129612 Hall of Fame Points: 3318 More actions · Answer 5

Create a new database with same physical file (mdf ,ndf ) name (on same path) and logical name.:)

Stop the sql server services.;)

replace the mdf and ndf files :w00t:

Start the SQL Services in single user mode with DAC account.You find the new database in Suspect mode.:cool:

Recover the database :hehe:

EXEC sp_resetstatus 'yourDBname';

ALTER DATABASE yourDBname SET EMERGENCY

DBCC checkdb('yourDBname')

ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE yourDBname SET MULTI_USER:)

Regards

Marimuthu