Attach SQL Server database with missing ldf file

This morning I was faced with an interesting issue. Somehow, I’m not entirely sure how, all the rows from one table in a database for a bespoke application I developed were missing. After spending an hour trawling through the logs of both SQL Server and the application’s logs I couldn’t figure out why they were missing. Luckily the application isn’t mission critical!

So, onto out backup server to restore the database from last nights backup. I performed a file level restore of the .mdf file but wait a minute, where the f**k is the .ldf file? Missing from the backup 😦

To restore the database as a single file copy the mdf file to the desired location and use the following query.

USE master
GO
CREATE DATABASE [TestDatabase] ON
(FILENAME = N’C:TempFolderSP_C_Intranet-Restored.mdf’)
FOR ATTACH_REBUILD_LOG
GO

Oh dear. it looks like the transaction large wasn’t committed before the backup was taken.

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.
Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘TestDatabase’. CREATE DATABASE is aborted.

There is a way around this issue but it may result in data loss so it is not recommended for a live environment. We can force SQL Server to rebuild the log using the following Query.

USE master
GO
CREATE DATABASE [TestDatabase] ON
(FILENAME = N’C:TempFolderSP_C_Intranet-Restored.mdf’)
FOR ATTACH_FORCE_REBUILD_LOG
GO

And that’s it. Now go and make sure your backups are running properly instead of pissing about.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s