Restoring a SQL Server Database that Is Missing the Log File

To restore Database without the LDF file:

1. Create a dummy database with the same name.
2. Stop SQL Server and replace the dummy MDF with the one in question, leaving the dummy LDF file intact.
3. Restart SQL Server – the database will appear in the Enterprise Manager with a gray icon, as "Suspicious".
4. Switch on the "Emergency mode" for this database by running the following script:
   

   EXEC sp_configure 'allow updates', 1
   RECONFIGURE WITH OVERRIDE
   GO
   
   BEGIN TRAN
   
   UPDATE master..sysdatabases
   SET status = status | 32768
   WHERE name = 'your_name_here'
   
   IF @@ROWCOUNT = 1
   BEGIN
      COMMIT TRAN
      RAISERROR('emergency mode set', 0, 1)
   END
   ELSE
   BEGIN
      ROLLBACK
      RAISERROR('unable to set emergency mode', 16, 1)
   END
   GO
   
   EXEC sp_configure 'allow updates', 0
   RECONFIGURE WITH OVERRIDE
   GO

5. Stop SQL Server.
6. Rename or remove the Log File.
7. Start SQL Server.
8. Create the new Log:
   

   DBCC REBUILD_LOG
   (
    'your_name_here',
    'C:\Program Files\Microsoft SQL Server\MSSQL\Data\your_name_here_Log.LDF'
   )
   

9. Set the Multi-User mode (otherwise the database will appear as (DBO Use Only):
   

   ALTER DATABASE your_name_here SET MULTI_USER

10. Remove the emergency mode:
    

    EXEC sp_configure 'allow updates', 1
    RECONFIGURE WITH OVERRIDE
    GO
    
    BEGIN TRAN
    
    UPDATE master..sysdatabases
    SET status = status & ~32768
    WHERE name = 'your_name_here'
    
    IF @@ROWCOUNT = 1
    BEGIN
      COMMIT TRAN
      RAISERROR('emergency mode removed', 0, 1)
    END
    ELSE
    BEGIN
      ROLLBACK
      RAISERROR('unable to remove emergency mode', 16, 1)
    END
    GO
    
    EXEC sp_configure 'allow updates', 0
    RECONFIGURE WITH OVERRIDE
    GO

* If for some reason it is impossible to rebuild the log file, after restarting SQL Server (step 7) the Import data functionality in Enterprise Manager will be available. It is useful to create another empty database and import the data and objects into this new database. Use the "Copy database objects" (third option) to copy tables, stored procedures and data. It may be necessary NOT to copy database roles and object-level permissions, as well as SQL server logins as this may cause the transfer to fail. Copying any stored procedures referring to non-existent db objects will also fail.

N.B. Graphical representation of a database running in Emergency mode is not available in Enterprise Manager, however, the structure of tables can be seen (and scripted!) in Query Analyzer.