Solved

sql log file missing

Posted on 2013-01-17
6
307 Views
Last Modified: 2013-01-22
Hi,

I had a problem with the partition that it had the log files of my databases, and I could not copy them outside of the partition. And I was forced to recreate the partition.

My mdf files are OK, but the databases in SQL 2008 are not accessible. I have created again the partition with the same letter and folder structure.

I tried to detach and could not do, so I stoped the sql service and then renamed the mdf file. After started the sql service I deleted the database in the sql. But now I cant attached as it is not accepting because of the log file, I even remove the log file entry in the attach option.

Is there any option left to me less then restore the backup of one day ago?

Regards
Joel
0
Comment
Question by:brithol
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38788875
Did a little search and found this article:

http://blog.sqlauthority.com/2010/04/26/sql-server-attach-mdf-file-without-ldf-file-in-database/

As he suggests, test thoroughly before doing anything to destroy your good mdf file.
0
 

Author Comment

by:brithol
ID: 38788945
hi,

I did what the article was saying and got these message:
File activation failure. The physical file name "I:\SQL_DB_FILES\CAVIS_PLUS1_1.ldf" may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'TestDb'. CREATE DATABASE is aborted.
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38789195
It sounds like your only option left is recovery from backup.

Paul Randal wrote about this kind of thing on this blog:

http://www.sqlskills.com/blogs/paul/creating-detaching-re-attaching-and-fixing-a-suspect-database/

The key concepts here are:

"Basically the problem is that the database wasn't cleanly shutdown, which means that recovery HAS to run and complete before the database can be attached again. Given that our log file is corrupt, that's impossible. "

and

" but it illustrates the point that after doing an emergency-mode repair, transactions that were active at the time the log was damaged will not get a chance to roll-back, most likely. "

Read the full article for the remarks in context, but the bottom line is that it doesn't look promising for you.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Accepted Solution

by:
brithol earned 0 total points
ID: 38789224
Hi I manage to get working my databases without loosing anything.
I stop the service of sql, change the mdf file, and then start the sql service. And created a new database with the same mdf filename, then stop the sql service, renamed the new one to another name and renamed the old one to the correct name. Started the sql service and run the script:
USE [master]
GO
ALTER DATABASE [MyDatabase] SET EMERGENCY
GO
ALTER DATABASE [MyDatabase] SET SINGLE_USER
GO
DBCC CHECKDB ([MyDatabase], REPAIR_ALLOW_DATA_LOSS)
GO
ALTER DATABASE [MyDatabase] SET MULTI_USER
GO
ALTER DATABASE [MyDatabase] SET ONLINE
GO

It is working fine....
Thanks
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38789270
OK what you have there is a database that's up and working but the fact that you allowed data loss (WITH REPAIR_ALLOW_DATA_LOSS) means exactly that.  SQL Server will do what it had to do in order to make the database come up.  If it had to delete certain things in order to make the database coherent again, then it would do that.

So while you may have your database up again, the fact that it wouldn't allow you to recreate the log file because it detected transactions that were in flight at the time of the crash means that you've probably lost some data.
0
 

Author Closing Comment

by:brithol
ID: 38804815
I manage to get the solution myself
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

628 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