?
Solved

How to restore sql server 2005 mdf file after a computer crash ?

Posted on 2007-10-01
14
Medium Priority
?
14,583 Views
Last Modified: 2009-02-24
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)
0
Comment
Question by:microplan
  • 5
  • 3
  • 3
  • +2
14 Comments
 
LVL 25

Assisted Solution

by:jogos
jogos earned 150 total points
ID: 19991611
If you don't have a 'backup' restore is difficult.

But you have de MDF-file so you can 'attach' the database. If you don't have the (matching) LDF-file it will be created.  

0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 600 total points
ID: 19991624
EXEC sp_attach_single_file_db @dbname = 'desiedDBName',
   @physname = 'fullpathOdMDF'

0
 
LVL 14

Accepted Solution

by:
twoboats earned 750 total points
ID: 19991640
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:microplan
ID: 19992048
Hello - thanks for responding !'

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

0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 600 total points
ID: 19992185
you just need to replace the values in Quotes with appropriate values
for example say you need to create a database 'MyDB' using the mdf stored in 'C:\mydb_Data.mdf'

EXEC sp_attach_single_file_db @dbname = 'MyDb'
   @physname = 'C:\mydb_Data.mdf'

you need to run this as  a new query in SQL Server management studio
0
 

Author Comment

by:microplan
ID: 19992397
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\WebRecherche\ContentSaver_WebRecherche_log.LDF" ist möglicherweise falsch.
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.
0
 

Author Comment

by:microplan
ID: 19992471
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'-Datenbank. (Microsoft.SqlServer.Smo)" Meaning: 'the backup file contains another database as the existing database ContentSaver_WR_2007..."
Can a restore a new (automatically created) database ?
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 600 total points
ID: 19992486
0
 

Author Comment

by:microplan
ID: 19992707
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 ...)
0
 
LVL 14

Assisted Solution

by:twoboats
twoboats earned 750 total points
ID: 19997282
sp_attach_single_file will only work if the file was unattached cleanly.

Steps in the case when this hasn't happened are

1) Move your mdf to safe location.
2) Create new databse of same name, same files, same file location and same file size.
3) Stop SQL server.
4) Swap mdf file of just created DB to your save one.
5) Start SQL. DB will go suspect.
6) ALTER DATABSE <your db> SET EMERGENCY
ALTER DATABASE <your db> SET SINGLE_USER
7) DBCC CHECKDB (<your db>, REPAIR_ALLOW_DATA_LOSS)
8) ALTER DATABASE <your db> SET MULTI_USER
ALTER DATABSE <your db> SET ONLINE
0
 

Author Comment

by:microplan
ID: 20000799
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.
0
 
LVL 14

Assisted Solution

by:twoboats
twoboats earned 750 total points
ID: 20004696
I don't think you can
0
 

Expert Comment

by:Mkatana
ID: 22110361
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
0
 
LVL 25

Expert Comment

by:jogos
ID: 22118777
Should be a new topic
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question