February 15, 2010 at 12:21 pm
had a hardware problem with a RAID array that holds static archive data a few months ago and i'm rebuilding manually just for the practice. works fine when the database is only a mdf file. last week i tried it with a database that has multiple ndf files and it wouldn't recreate the transaction log. is it possible or should i just restore from backup?
February 15, 2010 at 12:31 pm
Automatically creating a new log file is only possible when the database is cleanly shutdown - otherwise the database knows that it needs the log file to run crash recovery.
If the log file is damaged or unavailable, and recovery must be run, the only recourses are:
1) use EMERGENCY mode to access the database and extract out (transactionally inconsistent) data
2) use EMERGENCY mode and run emergency mode repair. That's not always guaranteed to work - I put it into the product as a last-ditch effort in extreme cases.
See CHECKDB From Every Angle: EMERGENCY mode repair - the very, very last resort for more details.
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
February 15, 2010 at 12:38 pm
used your method
i set up a VMWare instance with SQL on it and manually copied the files over.
created a db with the same name
shut down sql and renamed the files so that the "real" mdf file would "mount" when SQL was restarted
started SQL and put the db in emergency mode and ran dbcc checkdb.
it created the log file and then i detached and copied the files back to the physical server
except this one file. it's the first db with almost 10 files. an mdf and the rest are ndf. when i started SQL back up i got an error. will try it again and will post the error
February 15, 2010 at 9:55 pm
Take a look at this, see if the error you're getting is detailed and worked through. If not, post the error.
http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply