Solved

Cannot attached database

Posted on 2008-10-30
3
1,280 Views
Last Modified: 2008-11-11
My server crashed but manage to save all the data file but the problem is I can't attach to database now after reinstalling the server.

the following errors occured in different database when attach.

1) =======================================

TITLE: Microsoft SQL Server Management Studio
------------------------------

Attach database failed for Server 'XXXX'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0x00000000100000 in file 'C:\Documents and Settings\Administrator\Desktop\XXX_log.ldf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Operating system error 38(Reached the end of the file.) on file "C:\Documents and Settings\Administrator\Desktop\XXX_log.ldf" during ReadFileHdr.
Could not open new database 'XXX'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 823)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=823&LinkId=20476

2) =======================================

TITLE: Microsoft SQL Server Management Studio
------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\XXX.mdf is not a primary database file. (Microsoft SQL Server, Error: 5171)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=5171&LinkId=20476

I have googled and found some like

http://database.ittoolbox.com/groups/technical-functional/sql-server-l/attaching-a-database-in-an-mdf-file-1466972?cv=expanded

http://www.sqlnewsgroups.net/group/microsoft.public.sqlserver.server/topic14765.aspx

any other opinion?
0
Comment
Question by:saimatkong
  • 2
3 Comments
 
LVL 6

Expert Comment

by:bcsql
Comment Utility
Try attaching just the datafile without the log file. Your log file is corrupt.  sp_attach_db_single_file. if this does not work you probably have to go back to the last backup and restore it.

sp_attach_db [ @dbname= ] 'dbname'
        , [ @filename1= ] 'filename_n' [ ,...16 ]

EXEC sp_attach_db @dbname = N'AdventureWorks',
    @filename1 = N'c:\Program Files\Microsoft SQL Server
        \MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf',
    @filename2 = N'c:\Program Files\Microsoft SQL Server
        \MSSQL.1\MSSQL\Data\AdventureWorks_log.ldf' ;
0
 

Author Comment

by:saimatkong
Comment Utility
same error occur.
0
 

Accepted Solution

by:
saimatkong earned 0 total points
Comment Utility
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now