Solved

log and .ldf file

Posted on 2001-06-15
11
1,243 Views
Last Modified: 2008-03-17
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
Comment
Question by:brittwinn
11 Comments
 
LVL 2

Expert Comment

by:kiprimshot
ID: 6196933
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
 
LVL 3

Expert Comment

by:mgmanoj
ID: 6196984
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
 

Author Comment

by:brittwinn
ID: 6198303
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
 
LVL 2

Expert Comment

by:sefa
ID: 6199720
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
 
LVL 2

Expert Comment

by:ykchakri
ID: 6200710
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 8

Expert Comment

by:chigrik
ID: 6200864
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
 

Author Comment

by:brittwinn
ID: 6203122
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
 

Author Comment

by:brittwinn
ID: 6203150
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
 

Author Comment

by:brittwinn
ID: 6205118
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
 

Expert Comment

by:CleanupPing
ID: 9282201
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
 

Accepted Solution

by:
modulo earned 0 total points
ID: 10246188
PAQed, with points refunded (300)

modulo
Community Support Moderator
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now