microplan
asked on
How to restore sql server 2005 mdf file after a computer crash ?
Hello - my computer has crashed so I had to re-install the complete system incl sql server 2005 and tried to restore a mdf database file from a backup. Unfortunately, this is only a copied mdf file from the former working directory and the backed up 'ldf' file seems not to be the proper one. So sql server is not able to load / attach the old mdf file. There is a cryptic error message (when trying to open the mdf file) with a hyper link but when i click it, a microsoft web page opens with the message : there is not help text for this error.
Some weeks ago i also made a backup from the mdf file (using the sql server gui). The resulting backup file was surprisingly saved without an extension, so i do not know what kind of file this is.
Now I created a new empty database with the structure of the original one and tried to laod the backup from the sql server 2006 gui (testing different extensions for it, such as bak, dat, mdf etc), but sql server 2005 cannot restore it (shows only an empty structure resp does not recognize the file - the file's content looks 'binary' when I open it with an text(hex editor, but maybe the reason is that the original mdf file contained a lot of binary data, e.g. pictures).
Sorry, I'm not such an expert with sql server 2005 - i'm using the mdf file with an external document saving application (unfortunately, the guys from this application have also no idea how to restore the mdf file).
Can you help ?
Thanks in advance.
Klaus (Germany)
Technical details:
my system: win xp prof sp2 (.net 1.1 - 3)
2 GB RAM
500 Gb HD
mdf file is about 7 GB
sql server 2005 developer edition ((features like enterprise ed)
Some weeks ago i also made a backup from the mdf file (using the sql server gui). The resulting backup file was surprisingly saved without an extension, so i do not know what kind of file this is.
Now I created a new empty database with the structure of the original one and tried to laod the backup from the sql server 2006 gui (testing different extensions for it, such as bak, dat, mdf etc), but sql server 2005 cannot restore it (shows only an empty structure resp does not recognize the file - the file's content looks 'binary' when I open it with an text(hex editor, but maybe the reason is that the original mdf file contained a lot of binary data, e.g. pictures).
Sorry, I'm not such an expert with sql server 2005 - i'm using the mdf file with an external document saving application (unfortunately, the guys from this application have also no idea how to restore the mdf file).
Can you help ?
Thanks in advance.
Klaus (Germany)
Technical details:
my system: win xp prof sp2 (.net 1.1 - 3)
2 GB RAM
500 Gb HD
mdf file is about 7 GB
sql server 2005 developer edition ((features like enterprise ed)
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Hello -
>you need to run this as a new query in SQL Server management stud
Unfortunately, this did not work - same error as from the Attach-Command from the GUI, the message is:
'Dateiaktivierungsfehler. Der physikalische Dateiname "C:\Dokumente und Einstellungen\All Users\Dokumente\WebRecherc
Das Protokoll kann nicht neu erstellt werden, da die Datenbank nicht ordnungsgemäß heruntergefahren wurde. Meldung 1813, Ebene 16, Status 2, Zeile 1
Die neue 'NewSQL_MP'-Datenbank konnte nicht geöffnet werden. CREATE DATABASE wird abgebrochen.'
German text meaning: "File activation error physical ldf name is probably wrong (but I did not specify one ...). No protocol could be created as the database was not shut down properly. Error 1813 .. .CREATE DATABASE is terminated"
Sorry.
>you need to run this as a new query in SQL Server management stud
Unfortunately, this did not work - same error as from the Attach-Command from the GUI, the message is:
'Dateiaktivierungsfehler. Der physikalische Dateiname "C:\Dokumente und Einstellungen\All Users\Dokumente\WebRecherc
Das Protokoll kann nicht neu erstellt werden, da die Datenbank nicht ordnungsgemäß heruntergefahren wurde. Meldung 1813, Ebene 16, Status 2, Zeile 1
Die neue 'NewSQL_MP'-Datenbank konnte nicht geöffnet werden. CREATE DATABASE wird abgebrochen.'
German text meaning: "File activation error physical ldf name is probably wrong (but I did not specify one ...). No protocol could be created as the database was not shut down properly. Error 1813 .. .CREATE DATABASE is terminated"
Sorry.
ASKER
Hello again:
>If you don't have a 'backup' restore is difficult
Yes, I HAVE a backup - but how to restore ? From the SQL management Studio GUI the function 'recreate? database' (German: Datenbank wiederherstellen) does not work. When I try to restore to a new empty database (same structure as the old one, created with the corresponding document archiving application) the sql server error message is: 'Der Sicherungssatz enthält die Sicherung einer anderen Datenbank als der vorhandenen 'ContentSaver_WR_2007'-Dat
Can a restore a new (automatically created) database ?
>If you don't have a 'backup' restore is difficult
Yes, I HAVE a backup - but how to restore ? From the SQL management Studio GUI the function 'recreate? database' (German: Datenbank wiederherstellen) does not work. When I try to restore to a new empty database (same structure as the old one, created with the corresponding document archiving application) the sql server error message is: 'Der Sicherungssatz enthält die Sicherung einer anderen Datenbank als der vorhandenen 'ContentSaver_WR_2007'-Dat
Can a restore a new (automatically created) database ?
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Hello -
>try this
>http://www.spaceprogram.com/knowledge/sqlserver_recover_from_deleted_log.html
Sorry, did not work: Step 9: 'Switch to emergency mode ... right click ...': Termintes with: 'Error invalid SQL transaction and/or missing files.' So switching to the emergency mode is blocked ...
(I really do not understand why Microsoft makes recovering mdf files so complicated and does not provide a utility for that ...)
>try this
>http://www.spaceprogram.com/knowledge/sqlserver_recover_from_deleted_log.html
Sorry, did not work: Step 9: 'Switch to emergency mode ... right click ...': Termintes with: 'Error invalid SQL transaction and/or missing files.' So switching to the emergency mode is blocked ...
(I really do not understand why Microsoft makes recovering mdf files so complicated and does not provide a utility for that ...)
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Hello - thanks for helping. Meanwhile I stopped trying to recover the damaged database and have setup an older backup which works - and now have to add manually the missing data from the last months ...
Whatsoever - although the old database works: now I have to deal with a new problem: very high CPU load (so my pc reacts only very!!! slow). Obviously the full text indexing of the (large) database is starting again from the bottum up (task manager shows high CPU load for the service/priocess 'msftefd.exe' which obviously is the full text engine of SQL Server 2005). In the task manager I could set the priority for this process lower (but only one time, after reboot I tried it again, but it was no longer possible), but in the SQL Server config manager there is no possibility to change the priority of the full text engine. Does anyone have an idea how to reduce the priority of the full text engine (without disabling it) , so that I can really use my pc without waiting a lot after each click ... (or until the database is again full indexed - but when ??) ?
Thanks.
Whatsoever - although the old database works: now I have to deal with a new problem: very high CPU load (so my pc reacts only very!!! slow). Obviously the full text indexing of the (large) database is starting again from the bottum up (task manager shows high CPU load for the service/priocess 'msftefd.exe' which obviously is the full text engine of SQL Server 2005). In the task manager I could set the priority for this process lower (but only one time, after reboot I tried it again, but it was no longer possible), but in the SQL Server config manager there is no possibility to change the priority of the full text engine. Does anyone have an idea how to reduce the priority of the full text engine (without disabling it) , so that I can really use my pc without waiting a lot after each click ... (or until the database is again full indexed - but when ??) ?
Thanks.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
I have tried
EXEC sp_attach_single_file_db @dbname = 'MyDb'
@physname = 'C:\mydb_Data.mdf'
But it 's giving error Incorrect syntax near @physname
any advice?
Thanks
EXEC sp_attach_single_file_db @dbname = 'MyDb'
@physname = 'C:\mydb_Data.mdf'
But it 's giving error Incorrect syntax near @physname
any advice?
Thanks
Should be a new topic
ASKER
Let me answer in the above oder:
>...you can 'attach' the database. If you don't have the (matching) LDF-file it will be created
Trying this (from the SQL Sever Management studio - right click - attach ...) results in an error #1813 (error attaching mdf file, transaction error etc). i if also specify the ldf file it is rejected.
>EXEC sp_attach_single_file_db @dbname = 'desiedDBName',
@physname = 'fullpathOdMDF'
dbname: this is my backup file (or the mdf file which I backed up/copied ) !?
physname: the new empty mdf !?
whre do I do the EXEC ? (on command prompt , in which directory, or using - which - GUI ?
>Have a look at this ques
OK, i will analyze this (takes a 'bit' time)
Thanks to all.
Klaus