SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Urgent - Unable to restore/attach a MDF file
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 3

tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 07/23/2004 :  13:33:06  Show Profile  Visit tkizer's Homepage  Reply with Quote
You don't have to be familiar with the syntax as you can always look it up in the documentation.

Tara
Go to Top of Page

meenakshikalera
Starting Member

19 Posts

Posted - 07/23/2004 :  13:52:20  Show Profile  Send meenakshikalera a Yahoo! Message  Reply with Quote
hi tara!

i could do it,,,my synatax was wrong,....sorry for not following u properly n taking up ur precious time....

but this is the error i am getting...
i feel alll my tables are there...still i shall confirm...but do u think this error is important or may be i got this error because my ldf file was missing....

lemme know...
thanks a lot for all ur help...i appreciate!
meenakshi

Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL\data\ParisoTrucking_Log.LDF' may be incorrect.
New log file 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\ParisoTrucking_log.LDF' was created.
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 07/23/2004 :  14:28:10  Show Profile  Reply with Quote
New territory for me, but it isn't anything to do with the SPACE in the path is it?

Kristen
Go to Top of Page

meenakshikalera
Starting Member

19 Posts

Posted - 07/23/2004 :  14:29:27  Show Profile  Send meenakshikalera a Yahoo! Message  Reply with Quote
kristen, was this for me?
what space in the path? i am sorry i did not get u...
do reply back
thanks,
meenakshi
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 07/23/2004 :  14:45:58  Show Profile  Reply with Quote

'C:\Program Files\Microsoft SQL Server\MSSQL\data\ParisoTrucking_Log.LDF' 
Here ------^   and here ---^   ^--- and here too

Kristen
Go to Top of Page

meenakshikalera
Starting Member

19 Posts

Posted - 07/23/2004 :  15:00:55  Show Profile  Send meenakshikalera a Yahoo! Message  Reply with Quote
i don't think so...bcos, it itself gave this error message....saying this m,ay may be in error so this new file is created....in both the paths there are spaces...what say? do u think i shud retry something?

Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL\data\ParisoTrucking_Log.LDF' may be incorrect.
New log file 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\ParisoTrucking_log.LDF' was created.

lemme know,
thanks,
meenakshi.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 07/23/2004 :  15:05:51  Show Profile  Visit tkizer's Homepage  Reply with Quote
Show us the exact command that you are running.

Tara
Go to Top of Page

meenakshikalera
Starting Member

19 Posts

Posted - 07/23/2004 :  15:07:25  Show Profile  Send meenakshikalera a Yahoo! Message  Reply with Quote
EXEC sp_attach_single_file_db @dbname = 'mee', @physname = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\mee_Data.MDF'
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 07/23/2004 :  15:09:25  Show Profile  Visit tkizer's Homepage  Reply with Quote
Move the MDF to the root of C, then:

EXEC sp_attach_single_file_db @dbname = 'mee', @physname = 'C:\mee_Data.MDF'

Tara
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 07/23/2004 :  15:20:54  Show Profile  Reply with Quote
Did you copy the MDF file to a CD at some point? If you did, the read-only bit will be set on the datafile. When you try and attach the database, you will get an error. Check the properties on the MDF and see if it is set read-only.

Also, if this database had multiple logfiles previously, you won't be able to attach it using sp_attach_single_file_db. See http://support.microsoft.com/default.aspx?scid=kb;en-us;271223 for specifics.




-ec

Edited by - eyechart on 07/23/2004 15:28:34
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 07/23/2004 :  15:27:47  Show Profile  Reply with Quote
Actually I suspect that Spaces in Path is a red herring - ATTACH is a new command, I've only really had trouble with this in RESTORE scenarios.

Is it possible that SQL is trying to create the LOG in the original location - which is possibly now a non-existent location?

Perhaps try

EXEC sp_attach_db @dbname = N'mee', 
   @filename1 = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\mee_Data.MDF', 
   @filename2 = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\mee_log.LDF'

Make sure that the .LDF file does NOT exist on disk before using this command.

I don't suppose there is any chance that the original DB had multiple log files?

But basically I reckon there is a high chance that you are hosed because you didn;t do a DETACH originally ...

Kristen
Go to Top of Page

meenakshikalera
Starting Member

19 Posts

Posted - 07/23/2004 :  15:29:20  Show Profile  Send meenakshikalera a Yahoo! Message  Reply with Quote
its still not working from c: too.
tht same error comes...though i feel tables look proper....
just tht ldf error

meenakshi
Go to Top of Page

meenakshikalera
Starting Member

19 Posts

Posted - 07/23/2004 :  15:29:25  Show Profile  Send meenakshikalera a Yahoo! Message  Reply with Quote
its still not working from c: too.
tht same error comes...though i feel tables look proper....
just tht ldf error

meenakshi
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 07/23/2004 :  15:30:12  Show Profile  Reply with Quote
quote:
Originally posted by Kristen

Actually I suspect that Spaces in Path is a red herring - ATTACH

I don't suppose there is any chance that the original DB had multiple log files?




I just edited my earlier post with this same question. Microsoft has an article on a problem with sp_attach_single_file_db for databases that had multiple logfiles here: http://support.microsoft.com/default.aspx?scid=kb;en-us;271223


-ec
Go to Top of Page

meenakshikalera
Starting Member

19 Posts

Posted - 07/23/2004 :  15:33:10  Show Profile  Send meenakshikalera a Yahoo! Message  Reply with Quote
yes i did not do detach intially...before taking the backup...i just stopped the server n put my mdf fiel in program files... folder....so when i started enterprise manager the next time it gave me the errror of "SUSPECT"....

i did not copy it to a cd...
it is not a read only file...i just checked...
meenakshi
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 07/23/2004 :  15:37:43  Show Profile  Reply with Quote
googling found an interesting page here: http://www.spaceprogram.com/knowledge/sqlserver_recover_from_deleted_log.html

Here is the text from that website:

Recovering from a deleted log file on SQL Server

Introduction
This document will give instructions on how to recover from a deleted database log file on Microsoft SQL Server. The database this was done on was SQL Server 7 with SQL Server 2000.

You will get a suspect beside the name of the database when this happens.

You may be interested in the document if one of the following happened:

  • You're log file got too big so you decided to shutdown SQL Server, then delete the log file.
  • Salvage data from a damaged SQL data (.MDF) file. ( Someone emailed about this ).

That's the only reason I can think of right now and that is the reason I have come to do figuring this out, so here goes.

Instructions
If you have a recent backup of the database, USE IT! Forget about this article and do a normal restore procedure. Otherwise read on.

First of all, you are SOL if you want a full recovery. You just can't get all the data back because the log file itself contained a lot of transactions that may never have made it to the data file.

So anyways, I read just about everything possible on this topic and nothing worked, I mean nothing. I tried going into emergency mode, running stored procedures that did squat, using sp_detach_db, then sp_attach_db, etc, even trying db_rebuild_log() (the rebuild_log thing seems to be an undocumented feature that someone must have figured out... easter egg?? not really since there are some serious warnings against using it, but hey, when you're in dire straits, you'll try anything once, right?).

This restore procedure doesn't seem to be formally documented anywhere. I guess you're just not supposed to be this stupid, but everybody makes mistakes don't they? And hey, I'm no DBA or anything! I just use the damn things. ;-)

Anyways, on to the guts of the article. I am trying to make this as simple as possible and pulling this from memory so if there is something I am missing, please don't hesitate to e-mail us at: knowledge@spaceprogram.com.

There's one thing to note here and if someone could verify it, that would be great. First thing I'd like to verify is if you can just skip to step 9 right off the bat? So as soon as you get a suspect database, can you just start at step 9? Can someone please try that and let me know. SEE UPDATE: JAN. 6, 2003 BELOW

  • Backup the data (.mdf) file! Just in case. We take no responsibility for anything that happens following this procedure.

  • EXEC sp_detach_db 'dbname' -- this will detach the database from the server

  • Restart SQL Server
    The database may still be seen in enterprise manager, but just ignore it.

  • Create a new database with the same name or a different name. You will have to use a different physical file name, which is fine.

  • Stop SQL Server.

  • Rename the new data file that was created to something else (ex: add.bak to the end)

  • Rename the old data file that you want to restore to the name of the newly created file (the same name as the file you changed in the step above)

  • Start SQL Server
    Now the db will still be suspect but you now have a log file.

  • Switch to emergency mode on the database. You do this by doing the following:

    • Right click on the database root node in Enterprise manager and bring up the properties.

    • Under the Server Settings tab, check of "Allow modifications to be made directly to the system catalogs".

    • click ok

    • Now go to the master database and open the sysdatabases table.

    • Find the suspected database in here and modify the status column, setting it to: 32768. This will put it into emergency mode.

    • stop then start sql server
  • Now here's the tricky part and I'm not sure how this will work on a single install, i was lucky enough to have SQL Server 2000 installed. But anyways, open up the Import and Export Data (DTS) program from the start menu. And you want to copy data from the old database to a brand new one. Just copy tables and views. And voila, this should work smoothly. Let me emphasize should.


UPDATE: Jan. 6, 2003
I just received an email from someone who tried starting at step 9 and he said it worked. If I can get someone else to confirm that, then I'll get rid of 1-8.

UPDATE: June 18, 2003
It seems that some people can just do 9-10 and some have to do all the steps.

Emails Received in Response to this Article
There have been many emails we have received about this article both sharing their experience and praising the instructions.

Read the emails here: http://www.spaceprogram.com/knowledge/sqlserver_recover_emails_received.html





-ec

Edited by - eyechart on 07/23/2004 15:50:24
Go to Top of Page

meenakshikalera
Starting Member

19 Posts

Posted - 07/23/2004 :  15:40:24  Show Profile  Send meenakshikalera a Yahoo! Message  Reply with Quote
"The log files for that database are deleted, moved, or renamed."

this is the 3rd point on the link posted above...

since i did not have a ldf file to put in the folder....i did not take a backup of the ldf file...so may be thats the reason.

meenakshi
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 07/23/2004 :  15:41:45  Show Profile  Visit tkizer's Homepage  Reply with Quote
meenakshi, you didn't answer whether or not you had multiple LDF files for the one MDF. If you did, then you can not use that MDF without the LDF files.

ec posted this link about the problem:
http://support.microsoft.com/default.aspx?scid=kb;en-us;271223

Tara
Go to Top of Page

meenakshikalera
Starting Member

19 Posts

Posted - 07/23/2004 :  15:47:33  Show Profile  Send meenakshikalera a Yahoo! Message  Reply with Quote
no i did not have multiple log files...
meenakshi
Go to Top of Page

MuadDBA
Aged Yak Warrior

USA
628 Posts

Posted - 07/26/2004 :  17:07:04  Show Profile  Reply with Quote
I don't think there is any problem. You got an error saying that the log file (there is a pointer in the MDF file to the log file(s) associated with it) was not present, and it created a new log file. Your database attached properly (didn't it? you can see it now, right?) and you are off and running and can see your tables and stuff.

You should be fine.
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000