[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

LOST LOG FILE!  TRANSACTION LOG DELETED!

Posted on 2005-05-27
14
Medium Priority
?
290 Views
Last Modified: 2012-06-09
Please help everyone...

I deleted my transaction log file and do not have a backup.  I still have the mdf file for my database.  How do I get the DB back working again?


PANIC PANIC!
0
Comment
Question by:thcit
  • 7
  • 5
  • 2
14 Comments
 
LVL 43

Expert Comment

by:Eugene Z
ID: 14097259
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 14097302
but you can not delete any DB files if the DB is online.
Was it offline? or MSsqlserver service down?
0
 
LVL 3

Author Comment

by:thcit
ID: 14097614
Service was down.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 14097663
I agree, try using attach, **BUT MAKE A COPY OF THE .MDF FIRST**, because attach makes changes to it, and if the attach fails you'll want to go back to a "fresh" copy for another recovery try.
0
 
LVL 3

Author Comment

by:thcit
ID: 14097740
The DB is in suspect mode right now is there anything I need to do before I try to attach the mdf?
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 14097789
Stop SQL Server;
Make a copy of the .MDF file;
Restart SQL Server.

Btw, be sure to attach using Query Analyzer *not* Enterprise Manager.
0
 
LVL 3

Author Comment

by:thcit
ID: 14097853
This is what I got.

Could not open new database 'asdm'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'E:\Program Files\Microsoft SQL Server\data\MSSQL\data\ASDM_Log.LDF' may be incorrect.
0
 
LVL 3

Author Comment

by:thcit
ID: 14098281
I can get the DB to come up in Emergency Mode and I am able to read all tables, permissions, sp's, functions etc...  I happen to have a an older copy of the DB, is there anyway I can copy all of the objects from the Emergency DB to the older restored DB?
0
 
LVL 43

Accepted Solution

by:
Eugene Z earned 2000 total points
ID: 14098904
see the solution at:

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21112752.html?query=dbcc+rebuild+log+EugeneZ&clearTAFilter=true

try script  
DBCC REBUILD_LOG('MyDatabase','C:\MyDatabase.ldf')
example from:
from http://www.winnetmag.com/SQLServer/Article/ArticleID/26044/SQLServer_26044.html

LISTING 1: Undocumented DBCC Command REBUILD_LOG
EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO

BEGIN TRAN

UPDATE master..sysdatabases
SET status = status | 32768
WHERE name = 'MyDatabase'

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

-- Restart SQL Server at this point.

DBCC REBUILD_LOG('MyDatabase','C:\MyDatabase.ldf')


/*Perform physical and logical integrity checks at this point.
Bcp data out if your integrity checks demonstrate that problems exist.
*/

ALTER DATABASE MyDatabase SET MULTI_USER
GO

-- Set database options and recovery model as desired.
GO

0
 
LVL 3

Author Comment

by:thcit
ID: 14116477
OK guys, luckily I figured this one out and got it to work.  Thanks for the great comments, although none of them seem to work in this scenario, thanks anyway.

Here is what I did for future reference.

>I allowed updates to the master DB and Set the flag for the suspect DB to Emergency Mode like EugeneZ displayed.
EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO

BEGIN TRAN

UPDATE master..sysdatabases
SET status = status | 32768
WHERE name = 'MyDatabase'

>Then I performed a DTS Export of all data and objects of the suspect DB.
>I dettached the old DB in emergency mode.
>Then did a rename on the newly exported DB to the old DB's name.

NOTE***Only one thing to watch---Some of my Indexes were reset so I had to delete and recreate those indexes/constraint relationships.

Unless anyone objects I am granting points to EugeneZ for partial help in this solution. Although I figured a great deal out on my own I did use some of his code and one of his suggestions inspired an idea.  I am therefore granting him the points as a 'C'.  Not because of his lack of solution but because I believe he deserves some points rather than none at all.  I believe 1000 is adequate.  I am leaving this suggestion open for the next 4 days for any objections.

thcit



0
 
LVL 3

Author Comment

by:thcit
ID: 14116490
quick correction---I did not use the BEGIN TRAN for future researchers.
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 14117764
thcit:
What are you talking about - granting C,  you figured out your own,  he deserves, etc

Without my last post you would be in same situation without the database.
my 'partial help' <=> 90% of the result for your question

I see in your words big disrespect.


And good news for you thcit:
 I do not need your points- grant somebody else
 and keep your C for your self

One more thing keep in mind - EE experts help you for free in their free time
and if you plan to get help in future – be very nice and respectful


0
 
LVL 3

Author Comment

by:thcit
ID: 14139303
EugeneZ:

Sad to say that I had no idea that a C was such a big offense.  My appolgies.  After reconsidering the matter I am granting an A in effort to show appreciation of your time and quick response to the question.  Please accept and in future references I will be more cautious with my grades and more explanatory in my words.

thcit.
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 14139609
Accepted
Thanks
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

872 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