-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

closed as off-topic by swasheck, Max Vernon, Philᵀᴹ, Vérace, dezso Feb 26 '15 at 10:19

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – swasheck, Max Vernon, Philᵀᴹ, Vérace, dezso
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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

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