Solved

sql log file missing

Posted on 2013-01-17
6
290 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

867 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

22 Experts available now in Live!

Get 1:1 Help Now