Solved

Cannot attached database

Posted on 2008-10-30
3
1,303 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 6

Expert Comment

by:bcsql
ID: 22845305
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
ID: 22847195
same error occur.
0
 

Accepted Solution

by:
saimatkong earned 0 total points
ID: 22847326
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql server connection string in config file 4 34
SQL Convert rows to columns 5 30
VM SQL server license. 1 52
SQL - Subquery in WHERE section 4 25
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

738 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