Cant attach a DB - critical error
-
Hi!
I had a hardware crash and I had do reboot the system. When WinXP opened I tried to open an aplication that works with a SQL DB in MSDE and the DB as gone: the DB was dettached. Tryed to attach but it gives me the following error:
ERROR 823: I/O Error 38 (End of file reached.**) detected during read at offset 0x000000434f0000 in file 'D:\MARIETA_DATA.MDF'
I don't have any recent backup
The MDF and LOG files are available.
Any suggestions??
** Original message in portuguese: Final do ficheiro encontrado
-
It sounds like the database (mdf file) had increased in size just before the crash and the new disk clusters had either not been written to disk, or they were written to disk and not linked to the file in the FAT (File Allocation table).
If the clusters were written to disk you might be able to recover them by running CHKDSK /f in a DOS command window (Start - Run - CMD in Windows). This recovers the lost clusters as separate files, but it is unlikely you can add them back to the database file in the correct order.
All the changes to the database file might still be in the log file. But if the database file is corrupted the log file is also corrupt (as changes are always made to both at the same time). But the immediate problem is making the mdf file a valid file. You can always recreate the log file. Sorry, but I don't know enough about database recovery utilities to help with this.
If the log file is not corrupt and you have never done a transaction backup on the log file it might contain all the changes to the database since it was created (it depends on your database settings). You could try creating a new empty database with the same table structures, then restore the transaction backup into it. I am not sure of the exact procedure. See BOL for details on restoring transaction backups. You may have to backup your empty database first, then restore it in 'recovery mode', then restore the log file.
If you do have a backup, even if it is old, copy your current log and mdf file (as a precaution), delete the corrupt mfd file, in Enterprise manager right click on 'databases' beneath the server name, select all tasks and restore, select the old backup, click on the options tab and select 'Leave database nonoperational but able to restore transaction logs'. it should create the database as it was at the time of the backup. Then you just need to restore the transaction log.
Hope this helps
Peter
-
Thx Peter for your reply.
How can I restore de transation log if I don't have a recent backup. I searched in BOL for a restore of the Log File but I didn't find anything related (or it's very confusing).
Is there a way to restore the last Log file over the log of my old backup?
Thx in advance
-
hi pal
if U have MDF file in healty condition thats enough
Just follow the steps
first Create a New DataBase In the Same name it is is already exists then drop it
NOte: before dropping database copy .mdf file to another location
after the creation of the new database
use the following command
a)exec Sp_detach_db @dbname = 'DBname'
b)if the path of your mdf/ldf is c:\mssql\data.. then Delete the physical file dbname.MDF or use
c)copy your original .MDF File in to the samepath
c) Exec sp_attach_singleFile_Db @DbName = 'DbName',Physname = 'c:\mssql\data\dbname.mdf
if it succeeds mailme at vishu_g2003@REdiffmail.Com
-
hi pal
if U have MDF file in healty condition thats enough
Just follow the steps
first Create a New DataBase In the Same name it is is already exists then drop it
NOte: before dropping database copy .mdf file to another location
after the creation of the new database
use the following command
a)exec Sp_detach_db @dbname = 'DBname'
b)if the path of your mdf/ldf is c:\mssql\data.. then Delete the physical file dbname.MDF or use
c)copy your original .MDF File in to the samepath
c) Exec sp_attach_singleFile_Db @DbName = 'DbName',@Physname = 'c:\mssql\data\dbname.mdf
if it succeeds mailme at vishu_g2003@REdiffmail.Com
-
This is my third attempt to reply. My postings keep getting lost. Sooo, I have decided to type it in Notepad and paste it.
To restore the current transaction log into the 'old backup' of the database, that you will have to restore anyway, you will have to first back up the current transaction log. See Microsoft article http://support.microsoft.com/default.aspx?scid=kb;en-us;253817&Product=sql2k. This depends on using SQLServer 7 or 2K to do the backup. If you don't have access to either of these I don't know how you could do it. BUT, you can only back up the current transaction log when the database is corrupted, if:
a) the transaction log file is not corrupted (in your circumstances the transaction log is probably also corrupt - if MSDE was writing to the database file it was also writing to the log file when the PC crashed)
b) transactions are being recorded in the transaction log file (the file should be big) and a transaction backup has not been done since the last full database backup.
Once you have a back up of the transaction file, you can restore your old backup (in recovery mode) and then restore the transaction log backup you have just made.
In your case it seems likely that you will just have to restore the 'old backup'.
Someone else out there may know some tool that can be used to restore corrupt databases. Another option is to find a company that does data recovery. You can search for a local one on the net.
Regards
Peter
-
May be it is a permissions problem. You should also check out the new stored procedure for attching databases in SQL 2005. If the ldf file is missing you will get a error message as well.
Krishnaswamy Jayaram
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply