Having a problem attaching a database

My SQL Server crashed back in August. I was able to recover some of the data
but it was not worth using. I was better off just starting over from
scratch. However, I did save the recovered information in case I needed to
pull any of it for reference. Well, that is what I need to do and I am
having trouble attaching the database to pull the required information. I am
very green when it comes to this stuff. I am running SQL Server 2000 v5, SP4
on Windows 2000. Below are the steps that I have taken to try to retrieve
the information:
1. Copy the recovered database to a different drive and rename it to
eliminate any possibility of over-writing my current database information
2. Open SQL Server Enterprise Manager
3. Attach Database
4. Select the database that I want to attach (renamed file in new location)
5. I have to rename the "Current file(s) location" to the correct file name
for the mdf and ldf files. The ldf file is still not recognized because the
file does not exist and will be created when attached.
6. I change the "Attach as:" to match the file name and assign the
appropriate owner.
7. Click OK and a message appears asking if I want to create an ldf file.
8. I click Yes to have the file created and an error message pops up:
Error 5105: Device activation error. The physical file
'F:\Jobboss\PRODUCTION_log.LDF' may be incorrect. Could not open new
database 'Plant1_Shreve'. CREATE DATABASE is aborted. Device activation
error. The physical file 'F:\Jobboss\PRODUCTION_log.LDF' may be incorrect.
Plant1_Shreve is on drive E. The F drive is the location of the current
database files.
9. I click OK and another window pops up stating "Attach database has
failed". I click OK to this and I am back where I started
Please try sp_attach_db in "Query Analyzer" and see what happens.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"M Figy" <M Figy@.discussions.microsoft.com> wrote in message
news:85957AE4-6F99-4CA7-ADBE-E8FB534DD35D@.microsoft.com...
> My SQL Server crashed back in August. I was able to recover some of the
> data
> but it was not worth using. I was better off just starting over from
> scratch. However, I did save the recovered information in case I needed
> to
> pull any of it for reference. Well, that is what I need to do and I am
> having trouble attaching the database to pull the required information. I
> am
> very green when it comes to this stuff. I am running SQL Server 2000 v5,
> SP4
> on Windows 2000. Below are the steps that I have taken to try to retrieve
> the information:
> 1. Copy the recovered database to a different drive and rename it to
> eliminate any possibility of over-writing my current database information
> 2. Open SQL Server Enterprise Manager
> 3. Attach Database
> 4. Select the database that I want to attach (renamed file in new
> location)
> 5. I have to rename the "Current file(s) location" to the correct file
> name
> for the mdf and ldf files. The ldf file is still not recognized because
> the
> file does not exist and will be created when attached.
> 6. I change the "Attach as:" to match the file name and assign the
> appropriate owner.
> 7. Click OK and a message appears asking if I want to create an ldf file.
> 8. I click Yes to have the file created and an error message pops up:
> Error 5105: Device activation error. The physical file
> 'F:\Jobboss\PRODUCTION_log.LDF' may be incorrect. Could not open new
> database 'Plant1_Shreve'. CREATE DATABASE is aborted. Device activation
> error. The physical file 'F:\Jobboss\PRODUCTION_log.LDF' may be
> incorrect.
> Plant1_Shreve is on drive E. The F drive is the location of the current
> database files.
> 9. I click OK and another window pops up stating "Attach database has
> failed". I click OK to this and I am back where I started
|||Once I figured out how to use the sp_attach_db in Query Analyzer, I got the
same results as before. The syntax that I used follows:
EXEC sp_attach_db @.dbname = N'--Plant1_Shreve',
@.filename1 = N'E:\Program Files\jobboss\--Plant1_Shreve.mdf'
Did I do this right?
"wei xiao [MSFT]" wrote:

> Please try sp_attach_db in "Query Analyzer" and see what happens.
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "M Figy" <M Figy@.discussions.microsoft.com> wrote in message
> news:85957AE4-6F99-4CA7-ADBE-E8FB534DD35D@.microsoft.com...
>
>
|||Hi There,
You need to try using sp_attach_single_file_db (see BOL)
This allows the attaching of just the MDF file and creates a new Log file
for your DB.
HTH,
Cheers,
Des
On 24/11/2004 14:05, in article
D64E767D-2DDB-416B-8C77-9895131C7DC6@.microsoft.com, "M Figy" <M
Figy@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
> Once I figured out how to use the sp_attach_db in Query Analyzer, I got the
> same results as before. The syntax that I used follows:
> EXEC sp_attach_db @.dbname = N'--Plant1_Shreve',
> @.filename1 = N'E:\Program Files\jobboss\--Plant1_Shreve.mdf'
> Did I do this right?
> "wei xiao [MSFT]" wrote:
|||I really appreciate everyone's help but I am still getting the same error
message that refers back to the file PRODUCTION_Log.LDF. Let me try to make
my request as simple as possible. I apologize for my ignorance. I am try to
recover data from a database that was corrupted due to the server crashing.
Some of the corrupt data was recovered and the database files were stored in
another location. I want to pull this recovered data into a database with a
different name so that I do not risk overwriting any current information. I
need some detailed instruction on how to do this, if it is possible. Please
do not hesitate to ask me any questions to gather as much info that you may
need. If it may be easier, we can communicate via e-mail:
mfigy@.wauseonmachine.com
Thank you very much for your anticipated help. Mike
"Des FitzGerald" wrote:

> Hi There,
> You need to try using sp_attach_single_file_db (see BOL)
> This allows the attaching of just the MDF file and creates a new Log file
> for your DB.
> HTH,
> Cheers,
> Des
>
> On 24/11/2004 14:05, in article
> D64E767D-2DDB-416B-8C77-9895131C7DC6@.microsoft.com, "M Figy" <M
> Figy@.discussions.microsoft.com> wrote:
>
>
|||"M Figy" <MFigy@.discussions.microsoft.com> wrote in message
news:DBBF9AEA-54C0-4BB4-8FDE-1DAE25623175@.microsoft.com...
> I really appreciate everyone's help but I am still getting the same error
> message that refers back to the file PRODUCTION_Log.LDF. Let me try to
make
> my request as simple as possible. I apologize for my ignorance. I am try
to
> recover data from a database that was corrupted due to the server
crashing.
> Some of the corrupt data was recovered and the database files were stored
in
> another location. I want to pull this recovered data into a database with
a
> different name so that I do not risk overwriting any current information.
I
> need some detailed instruction on how to do this, if it is possible.
Please
> do not hesitate to ask me any questions to gather as much info that you
may
> need. If it may be easier, we can communicate via e-mail:
> mfigy@.wauseonmachine.com
The reality is.. you may not be able to attach the corrupted databases.
Your best bet is to call Microsoft Server Support with Credit Card in hand.
They have a few tricks that MAY get your DB back.