Attaching a db - missing log (ldf)

Hello,

I'm trying to re-attach sqlserver 2005 db. I have the mdf file but I don't have the log file. When I try to attach, I get the appended error.

Is there anyway, around this?

Thanks

Houman

TITLE: Microsoft SQL Server Management Studio

Attach database failed for Server 'SERVER'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Could not open new database 'fuzzy'. CREATE DATABASE is aborted.
File activation failure. The physical file name "C:\bin\dev\sqlServer2005\MSSQL.1\MSSQL\DATA\fuzzy_log.ldf" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down. (Microsoft SQL Server, Error: 1813)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=1813&LinkId=20476

BUTTONS:

OK

I also tried:

CREATE DATABASE fuzzy

ON (NAME='fuzzy', FILENAME='C:\temp\fuzzy\fuzzy.mdf')

FOR ATTACH_REBUILD_LOG

And recieved the error:

File activation failure. The physical file name "C:\bin\dev\sqlServer2005\MSSQL.1\MSSQL\DATA\fuzzy_log.ldf" may be incorrect.

The log cannot be rebuilt because the database was not cleanly shut down.

Msg 1813, Level 16, State 2, Line 1

Could not open new database 'fuzzy'. CREATE DATABASE is aborted.

Boy, this doesn't look good...anyway around this?

Thanks

Houman

|||

The database which u r trying to attach should be properly detached one. otherwise this will not work.

Madhu

|||The file you are trying to attach was not "detatched" first and will never work. You need to either detach it properly and then copy it, or restore from backup.

|||

I'm not a dba, I was following some dude's instructions on truncating the log file. I deattched the db using the GUI. Then deleted the log file (it was over 5 gb).

Is it possible that the cause is something else?

Thanks,

Houman

|||

if it was properly detached then this error will not occur... anyhow read sp_attach_single_file_db in BOL and try this sytem stroprocedure... But let me tell u , this is not the recommended way do shrink the log file. What u should do is

Backup database Somedatabase with truncate_only --

WITH TRUNCATE_ONLY --Removes the inactive part of the log without making a backup copy of it and truncates the log. This option frees space. Specifying a backup device is unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are synonyms.

DBCC SHRINKFILE (somelogical Log file Name, 100)

refer : http://support.microsoft.com/kb/873235

Read Shrinking the Transaction Log in BOL

|||We have found doing that procedure under 2005 does not work if you have more than 1 log file. It will not recreate more than 1 log.

If you detached it, you should not be getting that message. You are probably going to have to restore from backup.

In the future the best way to shrink the log file is to use the "shrink" method. You can do this automatically by setting up a "maintenance plan".

|||Connection of a file .mdf without a file .ldf

This page in Russian to translate it is possible here|||

Dear, the Article In Russian Is just for SQL SERVER 2000

it's not for SQL SERVER 2005

so, Can any body help me here?

I didn't dettach the data base and the log file was deleted....

oh....

I don't want to cry on it but is there an way to restor it....

I know it wasn't clearly shut down......

I used multiple ways and it didn't work....

for example

:

USE [master];

GO

Create DATABASE [pcrm]

ON (FILENAME = 'E:\pcrm.mdf')

FOR ATTACH_REBUILD_LOG

go

and this what I get every time:

The log cannot be rebuilt because the database was not cleanly shut down.

Msg 1813, Level 16, State 2, Line 2

Could not open new database 'pcrm'. CREATE DATABASE is aborted.

I even used the way in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=645631&SiteID=1 in despite of that I know it's for SQL SERVER 2000

and this gave me some error message I can not talk about

PLEASE HELP ME!!!,Thanks in advance....

|||

you can make use of this,

sp_attach_single_file_db [ @.dbname= ] 'dbname' , [ @.physname= ] 'physical_name'but its not possible for multiple data files.....also it wont work if the db was not clearly shudown

if that is not yielding the desired result try as mentioned below,currently if your db might be in suspect state, but it will not be shown in SSMS if thats the case,then start from step(f), if this also fails perform from step (a) to step(h)

(a) create a database with the same name in another directory as the one you're trying to attach
(b) re-create all filesgroups and files as necessary
(c) shutdown the server
(d) swap in the old mdf file and any ndf files
(e) bring up the server and let the database attempt to be recovered and then go into suspect mode
(f) put the database in single_user and emergency modes
(g) run DBCC CHECKDB (dbname, REPAIR_ALLOW_DATA_LOSS) which will rebuild the log and run full repair
(h) return database to online, multi_user mode

|||dear Mr.Deepak Rangarajan
i don't know how to thank you...
you really helped me....
it's done now....
the first and second solution didn't work with me but
doing all the steps from a to h
comes with the desired results....
now it's done.....
I advice all those who have the same problem to do this method and never listen to those that say it's impossible
Mr.Deepak Rangarajan
thank you again..