-3

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:

  1. 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

  2. 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

  • 6
    First you have to find out what version of SQL Server your mdf file belongs to. Then use CREATE DATABASE ... FOR ATTACH. What errors you get when you attempt to attach the mdf ? – Kin Shah Feb 24 '15 at 17:13
  • 4
    Why do you have an .mdf file, but no log file or backup? Were you "backing up" the database by saving a copy of the .mdf file somewhere, or by detaching? Have you learned a valuable lesson here? – Aaron Bertrand Feb 24 '15 at 17:47
  • 2
    @Quent:When you say you are unsuccessful what error you got, perhaps error would allow us to help you better. As you already read a SE thread which says that attaching without log would only work if database was shutdown cleanly. What was your case ? – Shanky Feb 25 '15 at 4:46
  • Shanky - We moved our SQL to a new server 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 – Quent Feb 25 '15 at 19:59
  • 3
    @Quent it's probably not a great idea to start off on a site by antagonizing the moderator who is trying to help you ask a better question. – JNK Feb 25 '15 at 19:59
4
 
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
| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.