Solved

sql log file missing

Posted on 2013-01-17
6
287 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
  • 3
  • 3
6 Comments
 
LVL 22

Expert Comment

by:Steve Wales
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Accepted Solution

by:
brithol earned 0 total points
Comment Utility
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
Comment Utility
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
Comment Utility
I manage to get the solution myself
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

771 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