Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • Last Modified:

sql log file missing

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
brithol
Asked:
brithol
  • 3
  • 3
1 Solution
 
Steve WalesSenior Database AdministratorCommented:
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
 
britholAuthor Commented:
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
 
Steve WalesSenior Database AdministratorCommented:
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
britholAuthor Commented:
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
 
Steve WalesSenior Database AdministratorCommented:
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
 
britholAuthor Commented:
I manage to get the solution myself
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now