Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Urgent - Unable to restore/attach a MDF file

Author  Topic 

Joozh
Posting Yak Master

145 Posts

Posted - 2004-07-07 : 01:30:53
Hi,

I was having problems on myt PC so i did a fresh installation. However I had backed up my SQL database (I only have the MDF file and not the log file).

I have tried restoring and attaching options but nothing works :(

I also get the error saying

"Can not open backup device.... Device error or device off-line. See the SQL server error log for more details. RESTORE database is terminating abnormally".

Please this is urgent. WIll be VERY grateful for your urgent reply.


Thanks & Regards.

-J

Kristen
Test

22859 Posts

Posted - 2004-07-07 : 03:39:21
> I had backed up my SQL database

Did you use the SQL BACKUP command (or BACKUP within Enterprise Manager) or just COPY the MDF file?

If you COPIED the MDF file was SQL Service SHUTDOWN at the time? or did you DETACH it first?


> I have tried restoring and attaching options but nothing works

What exactly did you try, and what error messages did you get pls

It looks as if you COPIED your MDF file and then used RESTORE to get it back - which would not be quite right, you would need to use ATTACH to get it back.

> See the SQL server error log for more details

Please check SQL Log (Enterprise Manager : Management : SQL Server Logs : "Current") and cut&paste any relevant messages here

(If you have stop/started SQL in the meantime you may need to look in one of the archive logs, or redo the "restore" operation to recreate the message in the logs)

(the messages should also be in EVENT LOG - applications)

Kristen
Go to Top of Page

Joozh
Posting Yak Master

145 Posts

Posted - 2004-07-07 : 09:03:58
Please let me trythis out and get back to you. What a day ...Ahhhhhhhhh :)

Thanks & Regards.

-J
Go to Top of Page

MuadDBA

628 Posts

Posted - 2004-07-07 : 13:11:18
It sounds like what you did was backed up the MDF file, and now you are trying to RESTORE it using the rESTORE command. That won't work.

To backup a database, you need to use the BACKUP DATABASE command, not simply copy the MDF file. The BACKUP DATABASE command makes a stable, reliable copy of the database which can be restored using the RESTORE command. Copying the .MDF file can work, but it has many more risks and I wouldn't recommend it.

To get the DB working with just the MDF file, execute the command:

SP_ATTACH_SINGLE_FILE_DB 'dbname','mdf file name' and, if it's at all possible, that will attach your database back. If it gives you errors, you're more than likely SOL on it, but pust them here and we'll try to help you.
Go to Top of Page

Joozh
Posting Yak Master

145 Posts

Posted - 2004-07-09 : 02:23:17
Thanks crazyjoe,

You are right and that was the problem. Well the problem is solved now and I really am very grateful to you and KRISTEN for taking the time to help and assist. REALLY appreciate this.

Regards.

Thanks & Regards.

-J
Go to Top of Page

meenakshikalera
Starting Member

19 Posts

Posted - 2004-07-23 : 12:24:14
i am having a similar problem....i just copied the .mdf file and now when i am putting it back in place....my database is not running...saying, "SUSPECT"

pls lemme a know a solution to this asap...

thanks,
regards,
meenakshi
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-23 : 12:34:01
meenakshi, you need to run sp_attach_single_file_db to attach the MDF file? It will create an LDF file for you in the same location.

Tara
Go to Top of Page

meenakshikalera
Starting Member

19 Posts

Posted - 2004-07-23 : 12:47:43
hi tara!

i read abt this command earlier too...but then where do i run this command?

meenakshi
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-23 : 12:48:48
In Query Analyzer

Tara
Go to Top of Page

meenakshikalera
Starting Member

19 Posts

Posted - 2004-07-23 : 12:52:39
tara...

Database 'ParisoTrucking' already exists.

this is the error that i am getting...

my server is running...enterprise manager is open too...
and i ahve already copied the mdf file tht i want to use in the proper program files folder...is this correct?

meenakshi
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-23 : 12:53:57
You need to delete the database before trying to attach it. First save the MDF file to a different location though. You could also try resetting the suspect status state as the database might be fine. You can do this with sp_resetstatus.

Tara
Go to Top of Page

meenakshikalera
Starting Member

19 Posts

Posted - 2004-07-23 : 13:07:39
tara...

i have a my mdf file in some place safe...
what i did now is as follows:

i first deleted the database say, "mee" from enterprise manager...
this deleted the "mee_Data.mdf" file from program files automatically...
i had earlier on tried to create a new database with the same name...this created a ldf file ...mee_Log.ldf in program files...tht same location...
so what has happened is... .mdf has gone but .ldf is still there...which is bacically nothing just 1024 kb in size...

now i tried to run the sp attach single command...but this time i got this error...
Device activation error. The physical file name 'mee_Data.mdf' may be incorrect.

whats happening?

meenakshi
Go to Top of Page

meenakshikalera
Starting Member

19 Posts

Posted - 2004-07-23 : 13:09:12
i forgot to mention tht before runnign this command...i had placed my mee_Data.mdf file in program files and then i ran the command.
meenakshi
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-23 : 13:09:54
Delete the LDF file, make a copy of the MDF file and place it in the directory where the LDF file was. Now run the sp_attach_single_file_db command. If that doesn't work, restore to the last full backup as the MDF file is corrupt.

Tara
Go to Top of Page

meenakshikalera
Starting Member

19 Posts

Posted - 2004-07-23 : 13:13:57
still getting the same error...device activation error...

is there any way out?
meenakshi
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-23 : 13:16:58
Probably not. In order for the MDF file to be a good file, you must have stopped the MSSQLSERVER service before doing the copy. You probably didn't do this, so this file was not ready to be copied at the file system. The file is corrupt.

Tara
Go to Top of Page

meenakshikalera
Starting Member

19 Posts

Posted - 2004-07-23 : 13:18:39
i did stop the server...
only thing tht i feel is...shud i have copied the corresponding ldf file too...

what can i do now...
i had converted these tables from access...
will i have to redo the whole thing?
meenakshi
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-23 : 13:19:29
You don't need the LDF file in order to attach the database. That's what sp_attach_single_file_db is for. Try attaching the MDF file to another server.

Tara
Go to Top of Page

meenakshikalera
Starting Member

19 Posts

Posted - 2004-07-23 : 13:27:02
i don't thing the mdf file is corrupt...
my supervisor had an older version of the mdf file...abt 7 days abck or so...i am trying to attach that too...getting the same device error....

i could be possible 2 files r wrong but also there is a probability of some other error...
ok listen...my database name is "mee"
i have a mdf file... "mee_Data.MDF" ok....
now my command would be...

SP_ATTACH_SINGLE_FILE_DB 'mee','mee_Data.MDF'

correct?

meenakshi
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-23 : 13:29:04
No, it would be:

EXEC sp_attach_single_file_db @dbname = 'mee',
@physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\mee_Data.mdf'

Change the path that I have posted to wherever the MDF is located.

Always check SQL Server Books Online for proper syntax. They usually give you examples in there as well. I copied the example directly into this post and modified it with what information you have provided.


Tara
Go to Top of Page

meenakshikalera
Starting Member

19 Posts

Posted - 2004-07-23 : 13:31:53
i was just doing tht...before u mailed
i read online n i did this
i am not so familiar with synatax...just started sql server...
Go to Top of Page
    Next Page

- Advertisement -