Attach a database without LDF | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Attach a database without LDF

I want to attach a database that one of the developers had detach it. The LDF file was deleted after detach. I tried to attach and rebuild the log file in SQL2005 using the following:
USE [master]
GO
CREATE DATABASE [Test] ON
(FILENAME = N’G:MSSQLDataTest.mdf’)
FOR ATTACH_REBUILD_LOG
GO
I get the following error:
File activation failure. The physical file name "D:MSSQLDATATest_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 1Could not open new database ‘Test’. CREATE DATABASE is aborted. What should I do? Thanks,
CanadaDBA
you could try sp_attach_single_file_db Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com
Thanks for the reply. I used the following code and got the same result.
USE [master]
GO sp_attach_single_file_db N’Test’, N’G:MSSQLDataTest.mdf’
GO Results:
File activation failure. The physical file name "D:MSSQLDATATest_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 ‘Test’. CREATE DATABASE is aborted. CanadaDBA
Try using Managment Studio without selecting ldf. I don’t try that yeat but with EM 2000 work without problems and a new ldf is created.
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS” with no warranties for accuracy.
Try CREATE DATABASE statement with ATTACH_REBUILD_LOG option…
In SQL 2000 you can use DBCC REBUILD_LOG command..which is in undocumented… In 2005 I believe you can ATTACH_REBUILD_LOG option…check BOL for more details… CREATE DATABASE pubs ON PRIMARY
(FILENAME =
‘C:program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatapubs.mdf’)
LOG ON (FILENAME =
‘C:program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatapubs_log.ldf’)
FOR ATTACH_REBUILD_LOG; MohammedU.
Moderator
SQL-Server-Performance.com
I believe Luis’s option should work with SSMS whereas EM used to have such issues. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
]]>