August 21, 2008 at 4:05 am
Dear All,
I physicaly deleted LOG file of one database. I have the MDF file with me now. I want to create a database to point to that MDF file..how can I do this..? Please help...
Thanks in advance..
Santhu.
August 21, 2008 at 4:09 am
nairsanthu1977 (8/21/2008)
Dear All,I physicaly deleted LOG file of one database. I have the MDF file with me now. I want to create a database to point to that MDF file..how can I do this..? Please help...
Thanks in advance..
Santhu.
You can use sp_attach_single_file_db to attach only the mdf (it will generate an ldf). For more information see http://msdn.microsoft.com/en-us/library/ms174385.aspx
Regards,
Andras
August 21, 2008 at 6:46 am
Provided the database was shut down cleanly, that is.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 21, 2008 at 7:03 am
GilaMonster (8/21/2008)
Provided the database was shut down cleanly, that is.
That is correct, thanks Gail. In case the database has not been shut down cleanly, you can still rescue most of the content of the backups via third party tools. If this is the case let us know.
- Andras
August 21, 2008 at 9:35 am
Even if it was not shut down cleanly, you can still recover your database without the log file. You may have some data loss and possibly some corruption. There was a recent post on the steps. If you cannot attach as previously suggested, post back here and I will try to find the article.
February 4, 2010 at 5:25 am
Good day all.
im having the same problem.
I lost my drive due to some hardware fault.
My log files were on the faulty drive.
i do not have access to a backup now.
i have the mdf file though,
The ldf files are gone.
Can i attach in sql 2005 to a single mdf file
The DB was not detached clean,
i tried the
exec sp_attach_single_file_db 'XXX','H:\AAA\xxx.mdf'
Msg 5173, Level 16, State 1, Line 1
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
Log file XXX.ldf' does not match the primary file. It may be from a different database or the log may have been rebuilt previously.
Msg 5173, Level 16, State 1, Line 1
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
File activation failure. The physical file name "xxx.ldf" may be incorrect.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'XXX'. CREATE DATABASE is aborted.
i also cant rebuild the log as that feature is not in sql 2005,
Please if someone had this problme , how did you get around, i need to attach my mdf , without ldf .
February 4, 2010 at 5:49 am
Please post new questions in a new thread. Thank you
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 5, 2010 at 4:51 am
Hi,
you just do the following steps.
Attach the data base --> Right click on Data base and choose attach. before this we need to complete following
1. Go to c:\Program files\Microsof Sqlserver\MSSQL.1\Data 2.place u r .mdf file
3.Rename u r log file if exists in same pplace
4.Then attach u r mdf file only.
5.Select log file row in attch data base window
6.click on bottom right corner Remove button.
7.click on ok
It goes smooth.
Regards,
Suresh
February 5, 2010 at 7:15 am
suresh.maddali (2/5/2010)
Attach the data base --> Right click on Data base and choose attach. before this we need to complete following1. Go to c:\Program files\Microsof Sqlserver\MSSQL.1\Data 2.place u r .mdf file
3.Rename u r log file if exists in same pplace
4.Then attach u r mdf file only.
5.Select log file row in attch data base window
6.click on bottom right corner Remove button.
7.click on ok
It goes smooth.
That only works if the database was cleanly shut down before the log was deleted. If it was not, then the attach will fail.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply