Restore db without ldf file, special case db was shutdown in mid tranaction

Question

Restore db without ldf file, special case db was shutdown in mid tranaction

mario17

SSCertifiable

Points: 6079
More actions
November 14, 2014 at 2:54 pm

#313033

Hi,
Got this situation, trying to do Use SignleMode to recover my handing db, after that lost ldf (and physically too).
Tried all things thru SSMS and scripts (below) that I know with no result, is there anything else I can try to recover it, I don't need log file.
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Could not open new database 'MyLostDB'. CREATE DATABASE is aborted.
File activation failure. The physical file name "C:\xxx\MyLostDB.ldf" may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure. (Microsoft SQL Server, Error: 1813)
EXEC sp_attach_single_file_db @dbname='Commissions',
@physname=N'C:\SQLData\MyLostDB.mdf'
GO
CREATE DATABASE Commissions ON
(FILENAME = N'C:\SQLData\MyLostDB.mdf')
FOR ATTACH_REBUILD_LOG
GO
Thanks Mario
August 29, 2018 at 3:11 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

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

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

You do need the log file. That's what the error message is telling you, the log file is not an optional part of the database.

Restore from your last good backup.

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

keithy_sunny Mr or Mrs. 500 Points: 566 More actions · Answer 2

You may try some third party tools. The one I have tried successfully in the past is DataNumen SQL Recovery at http://www.datanumen.com/sql-recovery/[/url] . You can use it to scan and recover the data from your corrupted MDF and NDF database files.

Good luck!

mario17 (11/14/2014)
Hi,
Got this situation, trying to do Use SignleMode to recover my handing db, after that lost ldf (and physically too).
Tried all things thru SSMS and scripts (below) that I know with no result, is there anything else I can try to recover it, I don't need log file.
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Could not open new database 'MyLostDB'. CREATE DATABASE is aborted.
File activation failure. The physical file name "C:\xxx\MyLostDB.ldf" may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure. (Microsoft SQL Server, Error: 1813)
EXEC sp_attach_single_file_db @dbname='Commissions',
@physname=N'C:\SQLData\MyLostDB.mdf'
GO
CREATE DATABASE Commissions ON
(FILENAME = N'C:\SQLData\MyLostDB.mdf')
FOR ATTACH_REBUILD_LOG
GO
Thanks Mario

DeWayne_McCallie Hall of Fame Points: 3617 More actions · Answer 3

How about this one:

USE master

GO

CREATE DATABASE <YOURDBNAME>

ON (FILENAME = 'D:\<YOURMDFNAME>.mdf')

FOR ATTACH_FORCE_REBUILD_LOG

mario17 SSCertifiable Points: 6079 More actions · Answer 4

mario17

SSCertifiable

Points: 6079

November 18, 2014 at 1:57 pm

#1759399

Thanks, DeWayne

This works !!!!

Mario

DeWayne_McCallie Hall of Fame Points: 3617 More actions · Answer 5

Awesome!

Just remember this is a undocumented option; however, you stated that you didn't need the log.

Thanks for replying back.

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

mario17 (11/18/2014)
Thanks, DeWayne
This works !!!!
Mario

It's also potentially left the database transactionally inconsistent and maybe even structurally inconsistent.

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