June 5, 2009 at 6:00 am
i have to mdf files in sql2000. as file1.mdf and file1_a.mdf.The ldf file of this file is lost as db got suspected.now while attaching the file1.mdf i get following error
error 5173 cannot associate files with different databases.
now how can i restore this db or attach it?
June 5, 2009 at 9:24 am
Do you have a backup?
Try using sp_attach_single_file_db
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
June 9, 2009 at 2:15 am
no i dont have backup and no log file also 🙁
June 9, 2009 at 2:36 am
Did attach_single_file_db work?
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
June 9, 2009 at 3:59 am
it didnt work,returned following error
I/O error 38(Reached the end of the file.) detected during read at offset 0000000000000000 in file Test_log.ldf
for the log file
June 9, 2009 at 4:13 am
sp_attach_single_file_db should rebuild the log, not go looking for it.
exec sp_attach_single_file_db @dbname = 'MyDB', @physname= '< Full path to file > file1_a.mdf'
If that doesn't work, I don't think you'll be able to get that DB attached.
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
June 9, 2009 at 5:27 am
You can use the same hack to get a database attached in 2000 as you can in 2005 (create dummy database with same number of files and then shutdown and swap in the corrupt files), but with the added complication that you have to force the database into emergency mode once attached, and then manually rebuild the log to create the new log file and run CHECKDB with REPAIR_ALLOW_DATA_LOSS. You'll attach the database and lose data but at least you'll get some back.
Don't have time to post detailed instructions now (that's a blog post not a forum post), but if you search around you'll find the right commands, or call Product Support to help you.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
June 9, 2009 at 5:32 am
-> copy the actual mdf files to a different location or rename it to _old.
-> note down the correct database name
-> create a new database with the same name and make sure that the log/data file name are also same as you had.
-> Stop SQL Server .
-> rename the MDF/ldf files to _new .
-> copy the mdf files to that location or remove the _old from it .
-> Start SQL Server
-> take database in to emergency
sp_configure 'allow updates',1
reconfigure with override
alter sysdatabases set status =32768 where name=
-> rebuild the log
DBCC rebuild_log ('test' ,'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\test.ldf')
-> Run checkdb on the database to see if there are any other consistency errors .
-> Delete any other unwanted file from the data folder .
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 9, 2009 at 5:34 am
Thats what Paul meant 🙂
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 9, 2009 at 5:59 am
Cheers - onsite at customer right now 🙂
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply