Attach Database Problem

Question

Attach Database Problem

devaraya

Default port

Points: 1455
More actions
April 2, 2006 at 10:21 pm

#190059

Hi Everybody,
Recently my SQl 7.0 was corrupted, so i re-installed sql 7 before that physically moved all mdf and ldf to different place. Now, the problem is when i am attaching the databases some of them are working fine and for some databases i am getting this error.
My T-Sql Command is like this:
exec sp_attach_db @dbname = 'garage', @filename1='e:\mssql7\garage__data.mdf',
@filename2='e:\mssql7\garage_log.ldf'
Error:
Server: Msg 9004, Level 21, State 1, Line 1
The log for database 'garage' is corrupt.
Connection Broken
I created one database with the name 'garage' and copied the old mdf file on the new one but it is also giving error. Is there any other way to attach the database? Thanks in advance.
Ramaa

Viewing 15 posts - 1 through 15 (of 19 total)

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

Vikas-299648 SSC Rookie Points: 32 More actions · Answer 1

Hi

Try to move log file to another location and while attaching database files specify only .mdf file. If SQL Server Creates new log for you, you will be lucky!!!

devaraya Default port Points: 1455 More actions · Answer 2

Hi Vikas,

I moved log file to some other location and i am attaching the database with this command.

EXEC sp_attach_single_file_db @dbname = 'garage',

@physname = 'e:\mssql7\garage_data.mdf'

I am getting this error.

Server: Msg 945, Level 14, State 2, Line 1

Database 'garage' cannot be opened because some of the files could not be activated.

Server: Msg 1813, Level 16, State 2, Line 1

Could not open new database 'garage'. CREATE DATABASE is aborted.

Ramaa

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 3

Hi,

RAMA

CAN YOU PROVIDE TO ME SUCH .mdf file , only then i can provide you to a best solution because i have to do some R & D on it.

Regards,

Amit Gupta

(M.C.D.B.A.)

SREENIVASARAO-314380 Valued Member Points: 64 More actions · Answer 4

SREENIVASARAO-314380

Valued Member

Points: 64

April 3, 2006 at 6:09 am

#630348

Hi

Try with Enterprize manager

Attach,detach

devaraya Default port Points: 1455 More actions · Answer 5

Hi Amit & Srinivasa Rao,

Thank you for your suggestions.

Amit I am unable to send the .mdf file because its company policy. Tell me how to do the job if you have any ideas?

regards

Ramaa

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 6

Hi,

Ok Then you can follow these steps and confirm to me it worked or not.

1) Start SQL Server in single user mode.

2) Go to your control panel and services.

3) Stop SQL Server

4) Add the -m switch in the parameters pane below.

5) Start SQL Server

6) Run sp_resetstatus with the @dbname parameter. (ie : sp_resetstatus @dbname = "pubs")

7) Perform detailed DBCC checks (CHECKDB, CHECKALLOC, etc)

Run a few random queries to see if you experience any problems.

If no problems occur, stop and start SQL Server and open the database to production.

As an absolute last resort, you can place your database in emergency mode. By placing it in this mode, you will be allowed to copy data out of the database, even if the data is corrupt. To place your database in emergency mode, use the following command:

SP_CONFIGURE 'allow updates', 1

RECONFIGURE WITH OVERRIDE

GO

UPDATE master..sysdatabases set status = -32768 WHERE name = 'pubs'

GO

SP_CONFIGURE 'allow updates', 0

RECONFIGURE WITH OVERRIDE

You can then BCP data out and place it into a different database.

Thanks,

devaraya Default port Points: 1455 More actions · Answer 7

Hai Amit,

Thank you friend for your explanation.

My database is not yet attached.How can i run sp_resetstatus on the database? I am newbie to sql server pls....What ever you gave the explanation for the database in suspect mode am i right?

regards

Ramaa

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 8

Hi Rama,

As you said that you are New So, You can't do it.

if you provide the mdf file to me than i sured to you i will attach it.

Regards,

Amit Gupta.

SQLBill SSC Guru Points: 51440 More actions · Answer 9

At this point, I think you are going to have to restore from your backups. (You DO have backups, don't you?) It looks like one or more of your databases were badly corrupted and is unrecoverable. Another thought, did you have .ndf files for those databases? Those are secondary files for SQL Server databases and have to be restored with the .mdf.

-SQLBill

SQLBill SSC Guru Points: 51440 More actions · Answer 10

Also, yes you are correct that Amit's directions are for a database that is attached and in suspect mode. So it won't work for you until you get your database attached.

-SQLBill

noeld SSC Guru Points: 96590 More actions · Answer 11

Try:

DBCC rebuild_log('<database name>','')

If the rebuilt action is good you need to run DBCC CHECKDB afterwards and possibly take your data out of that DB!! (DTS or bcp come in handy)

Regards,

* Noel

devaraya Default port Points: 1455 More actions · Answer 12

Hi Amit,

Sorry i am unable to reply you i was busy with some other work. Finally i got the backup from my sys admin and i restored the db successfully. Thank you for offering help and thank you Noel.I will do some R&D with all suggestions.

Ramaa

Andreas AJV SSC Eights! Points: 800 More actions · Answer 13

Fortunately, seems to have hapy end.... here.

But,.... i have some questions...

Under which circumnstances you SQL Server was corrupted?

How did u realise that all the DBs cannot be accessed?

The tools provided by SQL Server for restoring / rebuilding didnt provide any help?

Why you choose the solution of "Re-Installation"?

------------
When you 've got a hammer, everything starts to look like a nail...

devaraya Default port Points: 1455 More actions · Answer 14

Hi Andreas,

In EM i found some of the databases are in suspect mode. Then i did some sp_resetstatus and checkdb. After restarting the server, i am unable to start the server by giving the error like 17207 unable to access the server.Then i posted the problem in forums. I got some information from our friends but no use.I have to rush to up the server.. no way.I re-installed the sql 7.

Ramaa