Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1257
  • Last Modified:

log and .ldf file

I was trying to reduce the size of my .ldf file by stopping SQL Server, deleting the file, then restarting SQL Server.

I did the above. When I open Enterprise Mngr, the database has (suspect) next to the name, and (no items) is written below the database name.

By mistake, I did not make a copy of the .ldf file.
The .mdf file is still on the pc.

Can I get my database back?
If yes, how?

My main concern is to recover all of the tables, views, and stored procedures. I'm not overly concerned with the data within the tables.
0
brittwinn
Asked:
brittwinn
1 Solution
 
kiprimshotCommented:
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
0
 
mgmanojCommented:
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
0
 
brittwinnAuthor Commented:
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?
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
sefaCommented:
first take a look at following questions

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

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

http://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
 


0
 
ykchakriCommented:
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.

0
 
chigrikCommented:
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
0
 
brittwinnAuthor Commented:
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?
0
 
brittwinnAuthor Commented:
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?
0
 
brittwinnAuthor Commented:
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.
0
 
CleanupPingCommented:
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.
0
 
moduloCommented:
PAQed, with points refunded (300)

modulo
Community Support Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now