January 14, 2005 at 12:28 pm
I won't go into a long - and embarrassing - explanation, but I lost the log file on one of my db's. I detached the dB and tried to attach it again, and got following message:
Could not open new database 'FinanceDataMart'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'f:\Mssql_DATA\FinanceDataMart_log.LDF' may be incorrect.
How can I fool my dB into accepting a new and blank log file. I'm not worried about missing any transactions in the log file
January 14, 2005 at 12:50 pm
When you attach a properly detached database, SQL looks in SysFiles (or one of those) for the physical name and location of the other files used by the database, and attempts to use the files found there. If it can't find the log file, it creates one in the same folder as the database file. If it does find a file but discovers it's incompatible (i.e., not the correct log file--as defined by internal settings identifying both associated database and some form of timing/last used setting) you would get a message similar to the one you list. (Details may vary if you have complex file or file group configurations, but by and large this should hold true.)
However, if you stopped SQL Server, deleted the log file, and then started up again, well, I did that once (only once!) and fortunately had several backups to rely on. (There are some system commands that can be used to rectify the situation having to do with "recovering suspect databases", but I have no experience with these.) Think of it as, even though SQL Server is stopped, the data and log files are still connected, and you have to keep them together. (When dettached, the connection is safely shut down; it could be safely reopened or abandoned.)
Philip
January 14, 2005 at 2:31 pm
Hi Philip,
Well I did something like that - didn't properly detach dB. I believe it was aggravated by the fact that a backup job was trying to run, and had been for some time.
Anyway, I no longer have a log file. Any idea how I can get the dB back without the log file i.e. kill the link between the two?
I dodn't suppose there's anyway to import the data from the lost dB into a new dB?
Thanks
January 17, 2005 at 10:05 am
I've never had hands-on experience doing this; given that, here's how I'd proceed (messing around with a quickly created and corrupted "test" database).
sp_attach_db creates a formatted CREATE DATABASE statement. Mess around with that, see if any special clauses can handle exception situations.
One very rare state that databases can land in is "suspect", and vague memory tells me your problem is related with this state (as are others). Sift through Microsoft Technet and Knowledgebase (and, as ever, Google) for anything on the subject.
I once had to call MS tech support for help on something vaguely like this. The tech guy suggested an easy way (which worked) and an alternate approach that used all sorts of byzantine undocumented routines that, I think, incidentally regenerated the log file. That's why I'm sure there's a solution buried out there somewhere. As a last resort, you can always call in MS phone tech support ($250 per case a few years back--and they'd refund your money if it's an undocumented bug [which is why you always check technet and knowledgebase first!]). I'm not a Microsoft fanboy, but I've always been impressed with their tech support.
Ideally, someone who's hit this before will post advice. I'd sure like to know the quick solutions...
Philip
January 17, 2005 at 11:40 am
Have you considered sp_attach_single_file_db?
Steve
January 18, 2005 at 5:43 am
Yes, you can go ahead and use the sp_attach_single_file_db stored procedure and just refer the .mfd file. YOu can get the sntax of it from BOL.
It will automatically create .ldf file for your database.
Hope this helps.
Kishore
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply