Submit My Question - Recent Questions - Recent Answer

Msdn Tech >> SQL Server >> SQL Server Disaster Recovery and Availability

attaching DB without .ldf file ?

hi !!

I want to attach database to SQL Server, but I'hv only .mdf file.

I lost .ldf file, how should i attach this DB to server ?

Thanks

[174 byte] By [ShitalP] at [2008-2-27]

«« what is the matter in using guids in a smart client against server scenario?
»» vb.net 2005 code doesn't work in vb.net 2003, HELP!!!!!!!!!

# 1

Take a look at sp_attach_single_file_db (or CREATE DATABASE ... FOR ATTACH in SQL Server 2005).

Was the database shutdown cleanly?

PaulRandal-MSFT at 2007-8-30 >

top of Msdn Tech,SQL Server,SQL Server Disaster Recovery and Availability...

# 2

You can attach a database without the logfile by attaching, then clicking the logfile location, and click the remove button. After you click OK a new logfile will be created for your database, like so:

http://img.photobucket.com/albums/v472/Schmedrick/attach.gif

Andy_T at 2007-8-30 >

# 3

hi !!

thanks for reply.

I tried both the solution , but i am getting error -

File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL\data\Settings_Log.LDF" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down

I don't hv LOG file of database , only hv mdf file.

Is there any other solution

SPD

ShitalP at 2007-8-30 >

# 4

If the database wsa not cleanly shut down, and you do not have the log file, you will have some lost data and possible inconsistencies.

The best method to use depends on which version of SQL Server you're running. If you can let us know that, we'll take the next step.

KevinFarlee at 2007-8-30 >

# 5

I'm having a similar problem. I'm using sql server 2000 and my database has 2 log files. I lost one and when I trying attach without log file I got error !

Regards,

Alexandre A.

AlexandreAlbuquerque at 2007-8-30 >

# 6

ok - in that case, becase your database wasn't cleanly shutdown you'll need to use the last resort (assuming you're using SQL Server 2005)

create a database of equal size to the one you're trying to attach

shutdown the server

swap in the old mdf file

bring up the server and let the database attempt to be recovered and then go into suspect mode

put the database into emergency mode with ALTER DATABASE

run DBCC CHECKDB (dbname, REPAIR_ALLOW_DATA_LOSS) which will rebuild the log and run full repair

Your database will be available again but you'll have lost data and the data won't be transactionally consistent - see the following blog posts:

https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/15/632398.aspx

https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/16/633645.aspx

https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/18/636105.aspx

If you're on SQL Server 2000, you can still do this but you'll need to use the undocumented DBCC REBUILD_LOG at your own risk. If you're unsure about any of this, you should contact Product Support to help you.

Thanks

PaulRandal-MSFT at 2007-8-30 >

# 7

Hi ,
This is working fine. But i want to attach without shutdown the sql server. Is it possible.
Thanks.

Paul Randal - MSFT wrote:
ok - in that case, becase your database wasn't cleanly shutdown you'll need to use the last resort (assuming you're using SQL Server 2005)
create a database of equal size to the one you're trying to attach
shutdown the server
swap in the old mdf file
bring up the server and let the database attempt to be recovered and then go into suspect mode
put the database into emergency mode with ALTER DATABASE
run DBCC CHECKDB (dbname, REPAIR_ALLOW_DATA_LOSS) which will rebuild the log and run full repair
Your database will be available again but you'll have lost data and the data won't be transactionally consistent - see the following blog posts:
https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/15/632398.aspx
https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/16/633645.aspx
https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/18/636105.aspx
If you're on SQL Server 2000, you can still do this but you'll need to use the undocumented DBCC REBUILD_LOG at your own risk. If you're unsure about any of this, you should contact Product Support to help you.
Thanks

jayap at 2007-8-30 >

# 8

When I started SQL Server and tried following code:

Code Snippet

Use master

sp_configure 'allow updates', 1

reconfigure with override

sp_dboption 'MainBase', 'single_user', 'true'

alter database MainBase set EMERGENCY

DBCC CHECKDB('MainBase', REPAIR_ALLOW_DATA_LOSS)

I got an error:

Msg 922, Level 14, State 1, Line 1

Database 'MainBase' is being recovered. Waiting until recovery is finished.

What should I do?

XPEH at 2007-8-30 >

# 9

just wait for sometime as the database is currently recovering.i.e its rolling forward the committed transactions and rolling back the uncommited trans....it will recover soon

DeepakRangarajan at 2007-8-30 >

# 10

I was waiting for 11 hours but nothing happened. I think there is no point in waiting becouse the database isn't really recovering.

Is there any possibility to change sysdatabases.status = 32768 (emergency mode)? Currently sysdatabases.status = 70656

XPEH at 2007-8-30 >

# 11

i dont know whether you can change the db into emergency mode coz its currently recovering,anyways try

alter database dbname set emergency

if its not working try to bring the db online by,

restore database dbname with recovery but i am not sure if this works?...........cool

DeepakRangarajan at 2007-8-30 >

# 12