| 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 |
 |
|
|
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 |
|
|
|
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. |
|
|
|
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 |
|
|
|
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 |
|
|
|
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 |
|
|
|
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 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-23 : 12:48:48
|
In Query Analyzer
Tara |
|
|
|
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 |
|
|
|
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 |
|
|
|
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
|
|
|
|
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 |
|
|
|
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 |
|
|
|
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 |
|
|
|
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 |
|
|
|
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 |
|
|
|
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 |
|
|
|
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 |
|
|
|
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 |
|
|
|
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...
|
|
|
|
Next Page
|
Urgent - Unable to restore/attach a MDF file