Help!! Unable to re attach database

[gromit114]

To All,

We recently created a database with multiple log files to allow for overspill as the transaction log grows. over the weekend we had failures due to the transation logs to large.

We detached the database and deleted the transaction logs which we have done in the past. When we have come to reattch it has failed saying it cannot reattach without the log files.

Does anyone know a way of attaching the database using the .mdf file only.

Both sp_attach_db and sp_attach_singlefile_db have failed.

Any Help Would Be Much Apprieacted.

 

[MeanGreen]

If you do not have the log files, then you will have to use the sp_attach_single_file_db or go to your backup copy and restore. I would try the stored procedure first. Here is an example from BOL:

EXEC sp_attach_single_file_db @dbname = 'pubs',
@physname = 'c:\mssql7\data\pubs.mdf'

Remember that the C:\mssql7\data\pubs.mdf is the "PHYSICAL" location on the server that you have put the single file.

Hope this helps.

 

[gromit114]

Thanks MeanGreen,

However we have tried this and we get a message saying unable to attach to log files.

 

[MeanGreen]

If you use sp_attach_single_file_db, there should be no log files. Go into your storage area and rename the log files (or delete them, but I rarely delete anything when trying to recover), and try your sp_attach_single_file_db again. It sounds like you still have the log file in the "PHYSICAL" location.

Hope this helps.

 

[gromit114]

We have deleted the tranasation logs this is the main problem. We have multiple tranasation logs.

When trying to reattach we get the error message:

Device activation error. on both drives where the tranastion logs used to be before beig deleted.

Is their any other way of restoring from a .mdf file only.

 

[MeanGreen]

A device activation error is usually associated with a tape restore. Is this file the original mdf file or is it the dat file from tape backup? How big is the mdf file? Can you take the mdf file and copy it to a development server where you can try to reattach it there? Have you tried rebooting the server after you deleted the log files? Maybe there is some caching going on and it thinks there are still log files (this is a long shot).

Hope this helps.

 

[gromit114]

I have copied the mdf file to a new location on my laptop and tried to reattach from here but it is still looking for the log files.

It looks like the locations are embedded within the mdf file.

I think it is stale mate.

 

[MeanGreen]

I have moved many databases between development and production where they exist on different physical locations and not had this problem. Do you have SQL Server running on your notebook? If the mdf file is not too big, would you like to send it to me and I will try to attach it in my SQL Server? Last option is you have to restore from tape backup, so good luck! You might try doing a search on

http://WWW.GOOGLE.COM

with your error and see what others have to say about it.

Sorry, I can't help much more.

 

[MeanGreen]

Another question, did you recently start adding more logs to the database? Another note in the BOL is this:

Use sp_attach_single_file_db only on databases that have a single log file. Do not use this stored procedure on databases that have multiple log files.


So you may be in trouble after all.

Good Luck!

 

[Sammy145]

only thingg i can think of is are you running across a network

You might run
DBCC TRACEON (1807)

Run your Attach STATEMENT

DBCC TRACEOFF(1807)

SAM

 

[tlbroadbent]

Check the following Knowledgebase article. The bottom line is, "You cannot attach a database that has been created with multiple log files without also attaching all the log files" and "The sp_attach_single_file_db command is not intended to be used with databases that have multiple log files."

http://support.microsoft.com/default.aspx?scid=kb;en-us;271223

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[gromit114]

Good Morning Guys,

Thank you for all your suggestions yesterday in regard to the above matter. I managed to re attach the database yesterday afternoon after finding a process on the internet in a different forum.

It is undocumented commands for SQL which allow you to attach a database in emergency mode and then rebuild the tranasaction log however this is a last ditch attempt and should only be done as a last resort.

However it worked and we were able to retrieve all information lost on Friday.

Regards

 

[MeanGreen]

Gromit114,
Could you share that information in case anyone else has this problem? Even the URL link would be good, as I would be interested to see how it is done.

Thank you.