cannot attach mdf file

• vterziyski

  SSC Enthusiast

  Points: 123

  More actions

  November 21, 2013 at 9:10 am

  #279270

  Hi,

  I have the following problem: Due to a harddisk problem the database went into Suspect mode.

  I don't want to risk losing any data by trying to fix it directly on the server since there are no recent backups so I copied the mdf and ldf files to another computer. While trying to attach the files a message came out:

  Attach database failed for Server 'SALESIRM\SQLEXPRESS'. (Microsoft.SqlServer.Express.Smo)

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

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

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

  Program Location:

  at Microsoft.SqlServer.Management.Smo.Server.AttachDatabase(String name, StringCollection files, String owner)

  at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabaseData.PrimaryFile.Attach()

  at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabase.SendDataToServer()

  ===================================

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

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

  Program Location:

  at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

  at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)

  at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)

  at Microsoft.SqlServer.Management.Smo.Server.AttachDatabaseWorker(String name, StringCollection files, String owner, AttachOptions attachOptions)

  at Microsoft.SqlServer.Management.Smo.Server.AttachDatabase(String name, StringCollection files, String owner)

  ===================================

  SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:12459; actual 0:0). It occurred during a read of page (1:12459) in database ID 5 at offset 0x00000006156000 in file 'C:\Users\Terziyski\Desktop\insider\IrmCrm.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe 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.

  During redoing of a logged operation in database 'IrmCrm', an error occurred at log record ID (14623:1865:10). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.

  Could not open new database 'IrmCrm'. CREATE DATABASE is aborted. (.Net SqlClient Data Provider)

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

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

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

  Server Name: SALESIRM\SQLEXPRESS

  Error Number: 824

  Severity: 24

  State: 2

  Line Number: 1

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

  Program Location:

  at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

  at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

  at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

  at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

  at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)

  at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

  at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

  at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

  My question is: Is there a way to attach the mdf and ldf files or should I risk and try to remove the Suspect mode on the server?

  Regards

• March 12, 2014 at 12:49 am

  This was removed by the editor as SPAM

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  March 12, 2014 at 2:20 am

  #1696237

  prettsons (3/12/2014)

  ---

  You need to remove the suspect mode from the server first then you can attach the database.

  No.

  Suspect is a status that a database has, not a server and it's a smidgen hard to remove the suspect status from a database before it's attached.

  And as always recommending repair with data loss before even looking at the details of the corruption is a bad idea.

  4 month old thread, so I suspect the OP has resolved it one way or another

  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

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• March 12, 2014 at 9:48 pm

  This was removed by the editor as SPAM

• kemberlypaulo

  SSC Rookie

  Points: 44

  More actions

  September 6, 2014 at 11:52 am

  #1743681

  Recovery Toolbox for SQL Server is relevant and secure option. From here http://www.sql.recoverytoolbox.com/[/url]