Solved

sql log file missing

Posted on 2013-01-17
6
294 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

809 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