asked on

Cannot attached database

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?

bcsql

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' ;

saimatkong

ASKER

same error occur.

ASKER CERTIFIED SOLUTION

saimatkong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial