| Author |
 Topic  |
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
 Posted - 07/23/2004 : 13:33:06
|
You don't have to be familiar with the syntax as you can always look it up in the documentation.
Tara |
 |
|
|
meenakshikalera
Starting Member
19 Posts |
Posted - 07/23/2004 : 13:52:20
|
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. |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 07/23/2004 : 14:28:10
|
New territory for me, but it isn't anything to do with the SPACE in the path is it?
Kristen |
|
|
|
meenakshikalera
Starting Member
19 Posts |
Posted - 07/23/2004 : 14:29:27
|
kristen, was this for me?
what space in the path? i am sorry i did not get u...
do reply back
thanks,
meenakshi |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 07/23/2004 : 14:45:58
|
'C:\Program Files\Microsoft SQL Server\MSSQL\data\ParisoTrucking_Log.LDF'
Here ------^ and here ---^ ^--- and here too
Kristen |
|
|
|
meenakshikalera
Starting Member
19 Posts |
Posted - 07/23/2004 : 15:00:55
|
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. |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 07/23/2004 : 15:05:51
|
Show us the exact command that you are running.
Tara |
|
|
|
meenakshikalera
Starting Member
19 Posts |
Posted - 07/23/2004 : 15:07:25
|
| EXEC sp_attach_single_file_db @dbname = 'mee', @physname = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\mee_Data.MDF' |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 07/23/2004 : 15:09:25
|
Move the MDF to the root of C, then:
EXEC sp_attach_single_file_db @dbname = 'mee', @physname = 'C:\mee_Data.MDF'
Tara |
|
|
|
eyechart
Flowing Fount of Yak Knowledge
USA
3575 Posts |
Posted - 07/23/2004 : 15:20:54
|
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 |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 07/23/2004 : 15:27:47
|
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 |
|
|
|
meenakshikalera
Starting Member
19 Posts |
Posted - 07/23/2004 : 15:29:20
|
its still not working from c: too.
tht same error comes...though i feel tables look proper....
just tht ldf error
meenakshi |
|
|
|
meenakshikalera
Starting Member
19 Posts |
Posted - 07/23/2004 : 15:29:25
|
its still not working from c: too.
tht same error comes...though i feel tables look proper....
just tht ldf error
meenakshi |
|
|
|
eyechart
Flowing Fount of Yak Knowledge
USA
3575 Posts |
Posted - 07/23/2004 : 15:30:12
|
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 |
|
|
|
meenakshikalera
Starting Member
19 Posts |
Posted - 07/23/2004 : 15:33:10
|
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 |
|
|
|
eyechart
Flowing Fount of Yak Knowledge
USA
3575 Posts |
Posted - 07/23/2004 : 15:37:43
|
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 |
|
|
|
meenakshikalera
Starting Member
19 Posts |
Posted - 07/23/2004 : 15:40:24
|
"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 |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
meenakshikalera
Starting Member
19 Posts |
Posted - 07/23/2004 : 15:47:33
|
no i did not have multiple log files...
meenakshi |
|
|
|
MuadDBA
Aged Yak Warrior
USA
623 Posts |
Posted - 07/26/2004 : 17:07:04
|
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. |
|
|
|
Topic |
|
Urgent - Unable to restore/attach a MDF file
