September 13, 2007 at 11:30 am
Hello,
I just have my disk re-imaged, before that, I back up all the data files for SQL 2000, now I am trying to bring them back, however, I got error saying:
The file you specified is not a valid sql server database.
What I did is:
back up .mdf/ldf files
attach them after system is re-imaged.
The only change that might exist is: I applied SP4 after the new image. I might have an earlier version before the re-image.
September 13, 2007 at 12:06 pm
As long as you have a later service pack/hot fix level of the same SQL Server version you will not have an issue but even if not a later SP/HF level most times you are just fine. How are you trying to attach the database exactly?
September 13, 2007 at 12:26 pm
you have the files in the same directory, right ?
* Noel
September 13, 2007 at 1:02 pm
Before I sent the disk to be re-imaged, I stopped the database and then I burned all the database files to a DVD. Then after I got my machine back, I try to attach these database, which I did numerous time before without any problem.
I then uninstalled SQL2005, 2000, and re-installed SQL2000, and applied the same patch sp3a as the machine before reimage.
I'm still getting the same error.
Crazy! What's going on?
Thank you very much.
I did the attach database in EM.
September 13, 2007 at 1:18 pm
have you tried to attach just the mdf?
September 13, 2007 at 1:18 pm
All the databases are doing this? I really cannot think of anything that might be the reaosn unless they were still open when you backedup.
September 13, 2007 at 1:20 pm
All the databases are doing this. I stopped SQL before I burned the DVD.
September 13, 2007 at 1:26 pm
if the files are corrupt you may need to restore from a bak.
or you can try mssqlrecovery if you are in dire need.
its a tough price to pay for not backing up, but may work.
September 13, 2007 at 1:40 pm
you mention you uninstalled sql 2005 and then reinstalled 2000, are you sure the databases have not been backed up from a 2005 instance? if that is the case they will not restore on 2000.
September 13, 2007 at 11:33 pm
Hi,
Upgrade the server to SQL 2005 and the attached the backedup file. You must be able to fix in this way.
Regards
Kumar
September 17, 2007 at 2:20 pm
I opened a case in Microsoft but this is ridiculous, MS can't fix the problem. What they are thinking is the file is corrupted, but I burned not only database files, there are also thousands of files in other formats, only sql files not working now.
Does anyone have any recommendation?
Thanks lots.
September 17, 2007 at 5:34 pm
"Before I sent the disk to be re-imaged, I stopped the database and then I burned all the database files to a DVD. "
As you are discovering, this method of copying the datbase files does not work.
From Books On Line:
"sp_attach_db should only be executed on databases that were previously detached from the database server using an explicit sp_detach_db operation."
You have probable lost the data.
SQL = Scarcely Qualifies as a Language
September 17, 2007 at 10:35 pm
Sorry but I have to agree. If you did not use sp_detach_db then you probably can not attach. But for the sack of trying everything.
Create a shell database with the same name as the original with the same mdf and ldf file names. Detach the data files so SQL server knows they are detached then copy you backed up mdf and ldf files over the detached files then use sp_attach_db to try to reattach the files.
This should work if the files are not corrupt.
Hope that helps.
September 18, 2007 at 7:10 am
Too bad, your method is not working.
September 21, 2007 at 6:10 pm
Since things are 'hosed' already, try this:
Do what the previous poster said...create a shell database with the same name. However, DO NOT detach it. Stop the SQL Server services. Delete the .mdf/.ldf file you just created. Copy the .mdf/.ldf from the disk to the hard drive. Start the services.
Remember if you: You must:
Backup Restore
Detach Attach
Stop services Start services
Creating the shell db makes the Master database know that a database of that name exists and that it must start it. By replacing the shell files with the ones from the disk, the Master database MAY start them up.
Good Luck
-SQLBill
-SQLBill
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply