Create ldf file from mdf file

sp_attach_single_file_db [ @dbname = ] 'dbname'
    , [ @physname = ] 'physical_name'

Where do I type this? In query analyser?

If the database file is named

NDProjects_Data.MDF

how will the command above look?

Thanks

type this in query analyser

here is an example

Examples
This example detaches pubs and then attaches one file from pubs to the current server.

EXEC sp_detach_db @dbname = 'pubs'
EXEC sp_attach_single_file_db @dbname = 'pubs',
   @physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'

After using:-

EXEC sp_attach_single_file_db @dbname = 'NDProjects',
   @physname = 'F:\System Files\MSSQL Data\NDProjects_Data.MDF'

I am getting:-

Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'NDProjects'. CREATE DATABASE is aborted.
File activation failure. The physical file name "D:\MSSQL Data\NDProjects_Log.LDF" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.

A trick that may do it...

1) Copy the MDF file in a backup folder
2) Create a database with the same name than the original database (make sure you respect its physical pathes and names) .  The idea here is to try to use a dummy LDF file and the old MDF file
3) Detach the database
4) Delete the new MDF file and replace it with a copy of the old MDF file you have put in the backup folder
5) reattach both the old MDF file and the new LDF file...

Hope this helps...

I have done this, thought of this before, but it says it doesn't match of something to that affect.

I get the following error when reattaching:-

"An error occurred when attaching the database(s).  Click the hyperlink in the Message column for details."

There is no hyperlink.....

If that does not work, try this:

NOTE: These are last ditch effort steps and not guaranteed to work, but I have used them in the past to successfully recover from this situation.

Create a new database with the same name and file name
Stop SQL Server and replace the mdf fiel of the new database with the one you have (keep a copy of it)
Start SQL Server
The database will come up suspect
Run these commands in Query Analyzer:

USE MASTER
GO
EXEC sp_Configure 'ALLOW UPDATES', 1
GO
RECONFIGURE WITH OVERRIDE
GO
UPDATE SYSDATABASES SET STATUS = 32768 WHERE NAME = '<dbname>'
GO
EXEC sp_Configure 'ALLOW UPDATES', 0
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_DBOption '<dbname>', 'SINGLE USER', 'TRUE'
GO
DBCC REBUILD_LOG ('<dbname>', '<path>\<dbname>_Log.ldf')
GO
EXEC sp_Configure 'ALLOW UPDATES', 1
GO
RECONFIGURE WITH OVERRIDE
GO
UPDATE SYSDATABASES SET STATUS = 0WHERE NAME = '<dbname>'
GO
EXEC sp_Configure 'ALLOW UPDATES', 0
GO
RECONFIGURE WITH OVERRIDE
GO

EXEC sp_dboption '<dbname>', 'SINGLE USER', 'FALSE'
GO

Replace <path> and <dbname> with the full drive/path to where the log file and database name

I would run each step one at a time to verify that each one succeeds.

These steps could expose corruption that might exist in the database. If there is corruption, it is already there. These steps will not cause it, they will just expose it.

So now run DBCC Checkdb(dbname)

Look at the last lines of the output to see if there is any error messages and any inforamtion about what you can do to correct it.

These steps are usually a last ditch effor to get the database operational. They may not work but they will not make the situation worse. Just make sure you keep a backup copy of the mdf file in case this does not work.

You may need to stop and start SQL Server before running checkdb. If the database comes up suspect again then you will need to run these commands to run checkdb

EXEC sp_Configure 'ALLOW UPDATES', 1
GO
RECONFIGURE WITH OVERRIDE
GO
UPDATE SYSDATABASES SET STATUS = 32768 WHERE NAME = '<dbname>'
GO
EXEC sp_Configure 'ALLOW UPDATES', 0
GO
RECONFIGURE WITH OVERRIDE
GO

Then if checkb allows you correct any corruption problems then you will need to run this batch to get the database fully operational:

EXEC sp_Configure 'ALLOW UPDATES', 1
GO
RECONFIGURE WITH OVERRIDE
GO
UPDATE SYSDATABASES SET STATUS = 0WHERE NAME = '<dbname>'
GO
EXEC sp_Configure 'ALLOW UPDATES', 0
GO
RECONFIGURE WITH OVERRIDE
GO

Hi rboyd56

I get the following error when trying to run the first set of commands

Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
Server: Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed.
Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install.
Server: Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "D:\MSSQL Data\NDProjects_Data.mdf". Operating system error 5: "5(error not found)".
Server: Msg 945, Level 14, State 1, Line 1
Database 'NDProjects' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
Server: Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
sp_dboption command failed.
Server: Msg 2526, Level 16, State 3, Line 1
Incorrect DBCC statement. Check the documentation for the correct DBCC syntax and options.
Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
Server: Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed.
Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install.
Server: Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "D:\MSSQL Data\NDProjects_Data.mdf". Operating system error 5: "5(error not found)".
Server: Msg 945, Level 14, State 1, Line 1
Database 'NDProjects' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
Server: Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
sp_dboption command failed.

Can you post the commands you used?

I would suggest running each step indicividually. It is easier to see what is going on if one of them fails.

The link that aneeshattingal posted might be easier.

Absolutely fantastic! You would this SQL would let you at least look at it in emergency mode. Oh well, it works, got all my data back! :)