We help IT Professionals succeed at work.

SQL lost ldf files

Here is the problem.
SQL server freaked out. all the ldf files on a drive disappeared.

I have the mdf and ndf files for the db. How can i recreate the log file or get my database back up and running.

Yes i usually have backups. we moved to a new server a few days ago. The maintenance plans were not setup here so we dont have anything to fall back on.

Running sql server 2008 enterprise

All suggestions welcome.
Comment
Watch Question

Author

Commented:
no one from sql in tonight ?
Assuming there's no way to retrieve your log file and only as a last resource you can create a DB with the same name and size of the one you want to attach, stop the SQL server service, replace the mdf file of the new DB with the mdf and ndf file of the original DB, restart the SQL Server Service, and let the DB attmpt to recover. If it goes into suspect mode, put it into emergency mode using ALTER DATABASE, run DBCC CHECKDB (youDBName, REPAIR_ALLOW_DATALOSS) which will rebuild the log and carry out a full repair.

You'll get your DB back, though abviously all active transactions in your DB will be lost as you've already lost the log file.

My fantastic friend Paul Randal has published some very useful info in his blog which may be of interest to you too:
https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/18/636105.aspx
https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/16/633645.aspx
https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/15/632398.aspx

Hope that helps. ; )

Author

Commented:
trying this now

Author

Commented:
i did the first part created the db and now waiting for it to go into suspect mode... but nothing... should i just proceed to set it to emergency and run the  dbcc
If the db has come up and is ready just run the dbcc check db (without repair) to see if there are any problems at all. If it comes back with error messages the follow up with repair option as per my previous post. If on the other hand dbcc returns with no errors start enjoying your recovered DB.
CERTIFIED EXPERT
Top Expert 2012

Commented:
This is an old article (for SQL Server 2000) it may help:
Recovering from a deleted log file on SQL Server
http://www.spaceprogram.com/knowledge/sqlserver_recover_from_deleted_log.html

Commented:
>> I have the mdf and ndf files for the db. How can i recreate the log file or get my database back up and running.

You can use Create database with Attach_rebuild_log option. Here is the sample syntax for the same:

CREATE DATABASE EE
      ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\EE.mdf'),
      (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\EE_dev.ndf')
      FOR ATTACH_REBUILD_LOG ;
GO

The Log file is going to be built automatically once you execute the above query.

Author

Commented:
perfect fix

Explore More ContentExplore courses, solutions, and other research materials related to this topic.