log and .ldf file

Do you have a .bak file?

You can restore by right clicking your database in Enterprise manager and All Tasks/Restore Database..the .bak file may show up..

if not there may be some other suggestions

Hi This will help you to recover your database.

sp_attach_single_file_db
Attaches a database having only one data file to the current server.

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

Arguments
[@dbname =] 'dbname'

Is the name of the database to be attached to the server. dbname is sysname, with a default of NULL.

[@physname =] 'phsyical_name'

Is the physical name, including path, of the database file. physical_name is nvarchar(260), with a default of NULL.

Return Code Values
0 (success) or 1 (failure)

Result Sets
None

Remarks
When sp_attach_single_file_db attaches the database to the server, it builds a new log file and performs additional cleanup work to remove replication from the newly attached database.

Used sp_attach_single_file_db only on databases that were previously detached from the server using an explicit sp_detach_db operation.

Permissions
Only members of the sysadmin and dbcreator fixed server roles can execute this procedure.

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


Manoj

I have tried both suggestions above.

From Enterprise Manager, when I try to attach the db, I get the following...
When I select the .mdf file, two lines appear in the Original File Names portion of the Attach Database window -
1) The .mdf file I selected (MAMS_Data.mdf). This line has a green check next to it.
2) The previous(?) log file (MAMS_Log.ldf). This line has a red X in the check box.
When I say ok, I get the following message...
Error 1813 - The Physical file name c:\...\MAMS_Log.ldf may be incorrect.
The next msg box tells me attach database faild.

When I run the command from the SQL Analyzer window, the message in the result pane tells me the command ran successfully, but nothing changes in Enterprise Manager, even after I refresh.

Any other suggestions, or am I doing something wrong?

first take a look at following questions

https://www.experts-exchange.com/jsp/qShow.jsp?ta=mssql&qid=10086449

https://www.experts-exchange.com/jsp/qShow.jsp?ta=mssql&qid=10178950

https://www.experts-exchange.com/jsp/qShow.jsp?ta=mssql&qid=10160697

If you have backup,it is better to restore it then try
mgmanoj's suggestion ;

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

How about trying 'Restore database <DBname> with RECOVERY'
You dont have to give any backup file name here. It will normally work for me, if the database goes into 'Suspect' due to an interupted backup process etc.

To reset suspect status, try this update statement:

UPDATE master..sysdatabases SET status = status ^ 256 WHERE name = <dbname>

Read about "My SQL Server database has been marked "suspect" - what can I do?"
http://www.windows2000faq.com/Articles/Index.cfm?ArticleID=14047

and

INF: Bypass (Emergency) Mode and DUMP TRANSACTION WITH NO_LOG
http://support.microsoft.com/support/kb/articles/Q165/9/18.ASP

Hope this helps

Thanks for the info, but please note...
All that I now have is a .mdf file. The I cannot attach or bring the db online to get to the point of the database being suspect. Any suggestions how I get it to be suspect?

Thanks for the info, but please note...
All that I now have is a .mdf file. The I cannot attach or bring the db online to get to the point of the database being suspect. Any suggestions how I get it to be suspect?

I ended up calling Microsoft. It took about an hour, but with some undocumented procedures, we were able to take the .mdf file and restore the database with a new .ldf file.

brittwinn:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.