Trying to attach a .mdf with missing .ldf

Question

I am trying to attach a .mdf database file to a SQL Servre 2005 however we do NOT have the .ldf and have been unsuccessful in all our attempts to date. I should also mention there is no back-up to pull from in this case.

Here is what we have tried to date:

Tried the below which created the Database and the Log file, however none of the data was there.

Attaching a MDF with a missing LDF
We also tried the below however this does not seem to work in 2005.

http://blog.sqlauthority.com/2007/07/21/sql-server-fix-error-msg-1813-level-16-state-2-line-1-could-not-open-new-database-yourdatabasename-create-database-is-aborted/

Any other suggestions?

How we got into this predicament

We moved our SQL Server to a new server machine and moved to a more current version. We detached all databases successfully (with no errors), moved things over and successfully reattached on the new server. Upon doing so we noticed that one of the databases did not get attached. Upon further inspection we noticed that the .ldf file was on the NAS where it ought to be. We searched our entire network, but it is just nowhere to be found. Using the above steps we've tried attached the .mdf and created a new .ldf however when one goes to the database it is empty eventhough the .mdf is 730,624KB

ooutwire · Answer 1 · 2015-02-24T17:19:14.640

 
CREATE DATABASE [your_database_name_here] ON
(
  FILENAME = N'C:\sql server\nologfile.mdf'
) FOR ATTACH_REBUILD_LOG; 
GO

-- Use this 'ATTACH_REBUILD_LOG' when you have one or more log files. 'FOR ATTACH' works with one and only one log file

A POSSIBLE TEST DEMO...

USE [master];
GO

IF DATABASEPROPERTYEX(N'TlogDemo' , N'Version') > 0 
      BEGIN
            ALTER DATABASE TlogDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
            DROP DATABASE TlogDemo;
      END;
GO

CREATE DATABASE TlogDemo 
ON PRIMARY
(
      NAME = N'Data'
    , FILENAME = N'C:\database files\nologfile.mdf'
)
LOG ON  
(
      NAME = N'Log'
    , FILENAME = N'C:\database files\nologfile.ldf'
    , SIZE = 1MB
    , FILEGROWTH = 1MB  
);
GO

-- Detach the database
EXEC sp_detach_db 
      @dbname = 'TlogDemo';
GO

-- Go and delete the transaction log file manually
--

-- Re-attach the database
CREATE DATABASE TlogDemo ON
(
    FILENAME = N'C:\database files\nologfile.mdf'
) FOR ATTACH_REBUILD_LOG; -- Use this 'ATTACH_REBUILD_LOG' when you have one or more log files. 'FOR ATTACH' works with one and only one log file
GO

Trying to attach a .mdf with missing .ldf

1 Answers1