Problem in attaching database

Question

Post reply

Problem in attaching database

Sanjoy Jana

SSC-Addicted

Points: 420
More actions
May 25, 2007 at 5:39 am

#63074

Hi all,
I am facing a problem during attaching a database (.mdf and .ldf files are copied fro another server). the message I am getting as follows:
I am new to MS SQL Server and unable to solve the problem. Can anyone help me in this metter.
Thanks in advance.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

-=JLK=- SSCertifiable Points: 5315 More actions · Answer 1

I'm not seeing the bit map you posted. Try just copying the text error message and posting that.

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

Please type in the error message and send any other information we might be able to use.

Sanjoy Jana SSC-Addicted Points: 420 More actions · Answer 3

sorry for that. following is the are messages:

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

Attach database failed for Server 'CTPL-1'. (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)

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

Could not find row in sysindexes for database ID 8, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.

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

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

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

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

Server Name: (local)

Error Number: 602

Severity: 21

State: 50

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)

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

********************************

can someone help in this matter. the databas is MS SQL 2000 developer edition.

Thanks in advance.

-=JLK=- SSCertifiable Points: 5315 More actions · Answer 4

Well I assume by the output that you are doing this through an external application, not Enterprise Manager or Query Analyzer. My first suggestion is to run the sp_attach_db command via Query Analyzer and see what error message you get, if any. If you still get an error, then post the complete command and error message. Either the database is corrupt, missing pieces or you could simply have the path to the files wrong. Please post ENTIRE command you issue that will help diagnose the problem.

James.

Sanjoy Jana SSC-Addicted Points: 420 More actions · Answer 5

Hi James,

Thanks for your reply. the previous post's messages was from 'Microsoft SQL Server Management Studio Express'.

this time i have followed the steps suggested by you and the followings are the command and mesage from Query Analyzer:

*****************************************

command:

-- =============================================

-- Attach database via sp_attach_db

-- =============================================

EXECUTE sp_attach_db @dbname = N'mci',

@filename1 = N'd:\ifgl\database\mci.mdf',

@filename2 = N'd:\ifgl\database\mci_log.ldf'

GO

*****************************************

message:

Server: Msg 602, Level 21, State 50, Line 1

Could not find row in sysindexes for database ID 8, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.

Connection Broken

*******************************************

if you need any more info to understand the problem, please tell me.

thanks in advance.

Sanjoy Jana.

Sanjoy Jana SSC-Addicted Points: 420 More actions · Answer 6

Hi all,

Can anybody give some quick solution to the above problem?

regards,

jezemine SSCrazy Eights Points: 8059 More actions · Answer 7

did you detach before you took copies of the mdf/ldf? if not then they will not be valid.

I suggest you restore from a backup instead, it's a more foolproof method.

Restoring froma backup doesn't require the database to be taken offline. to do the detach/attach method, you have to detach first, which means the db needs to be taken offline.

---------------------------------------
elsasoft.org

-=JLK=- SSCertifiable Points: 5315 More actions · Answer 8

Sounds like something corrupted in the database. Did you run sp_detach_db before copying the database files to the new server? I've never encountered a problem after detaching a database (doesn't mean it can't happen). Can you reattach the database on the original server? If database still available on original server I recommend running DBCC CHECKDB ('DatabaseName'), correcting any problems (if any) found and then trying again. You also might consider just using backup and restore commands. I'm not sure how to get around the error in the copy of the database you have, without being able to attach it I don't know of any tools available to fix internal problems.

James.

jezemine SSCrazy Eights Points: 8059 More actions · Answer 9

my money says the OP didn't detach first, they just copied the ldf/mdf while the db was still attached.

---------------------------------------
elsasoft.org

JBStyle SSC Rookie Points: 30 More actions · Answer 10

The problem is related to an improper detach or if you had stopped the services on one server and copied the .mdf & .ldf files to another server then run sp_attach.

Rename the Log file and then try the sp_attach.. command again. What will happen is that SQL Server will prompt you to let you know that the log file is not there and then it will create a new log file as part of the attach.