Restoring from MDF and LDF

Hi,
Our hard disk parition failed and the only thing we can recover were MDF and
LDF files for SQL Server Database.
How do we recover the database from these files?
When we try using sp_attach_db we get this error :
Could not open new database 'name'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'D:\MSSQL\data\name_Log.LDF'
may be incorrect.
We do not have D:\ drive any more everything is in C:\ now.
Any help would be appreciated...
Thanks
JKDid you try attaching them?
http://www.aspfaq.com/
(Reverse address to reply.)
"JK" <JK@.discussions.microsoft.com> wrote in message
news:C5B5AEC5-94AC-4F62-9592-0646D625F151@.microsoft.com...
> Hi,
> Our hard disk parition failed and the only thing we can recover were MDF
and LDF files for SQL Server Database.
> How do we recover the database from these files?
> When we try using sp_attach_db we get this error :
> Could not open new database 'name'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
'D:\MSSQL\data\name_Log.LDF' may be incorrect.
> We do not have D:\ drive any more everything is in C:\ now.
> Any help would be appreciated...
> Thanks
> JK|||Can you somehow add a D: drive, place the files in the right path and try
attaching again.
Or else, find another SQL Server with D: drive and try attaching these
databases to that server.
Note that, if you haven't detached these databases previously, you may not
be able to attach them successfully.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"JK" <JK@.discussions.microsoft.com> wrote in message
news:C5B5AEC5-94AC-4F62-9592-0646D625F151@.microsoft.com...
Hi,
Our hard disk parition failed and the only thing we can recover were MDF and
LDF files for SQL Server Database.
How do we recover the database from these files?
When we try using sp_attach_db we get this error :
Could not open new database 'name'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'D:\MSSQL\data\name_Log.LDF'
may be incorrect.
We do not have D:\ drive any more everything is in C:\ now.
Any help would be appreciated...
Thanks
JK|||Yes I tried using Enterprise Manager and the Attach Database as well as T-SQ
L sp_attach_db
"Aaron [SQL Server MVP]" wrote:

> Did you try attaching them?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "JK" <JK@.discussions.microsoft.com> wrote in message
> news:C5B5AEC5-94AC-4F62-9592-0646D625F151@.microsoft.com...
> and LDF files for SQL Server Database.
> 'D:\MSSQL\data\name_Log.LDF' may be incorrect.
>
>|||I tried this too then it gives error:
An error occurred while processing the log for database 'name'
Connection broken
"Narayana Vyas Kondreddi" wrote:

> Can you somehow add a D: drive, place the files in the right path and try
> attaching again.
> Or else, find another SQL Server with D: drive and try attaching these
> databases to that server.
> Note that, if you haven't detached these databases previously, you may not
> be able to attach them successfully.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "JK" <JK@.discussions.microsoft.com> wrote in message
> news:C5B5AEC5-94AC-4F62-9592-0646D625F151@.microsoft.com...
> Hi,
> Our hard disk parition failed and the only thing we can recover were MDF a
nd
> LDF files for SQL Server Database.
> How do we recover the database from these files?
> When we try using sp_attach_db we get this error :
> Could not open new database 'name'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'D:\MSSQL\data\name_Log.LD
F'
> may be incorrect.
> We do not have D:\ drive any more everything is in C:\ now.
> Any help would be appreciated...
> Thanks
> JK
>
>|||Are you at all familiar with DBCC REBUILD_LOG ? It saved my bacon once
with minimal data loss.
In article <1A30B41D-8840-4837-BE9C-588A3373C87B@.microsoft.com>,
JK@.discussions.microsoft.com said...
> I tried this too then it gives error:
> An error occurred while processing the log for database 'name'
> Connection broken
> "Narayana Vyas Kondreddi" wrote:
>
>|||Hi JK,
Copy the original MDF and LDF to a safe location and try attaching the
database only with MDF file. This will not work if you have more
than 1 MDF and LDF files.
EXEC sp_attach_single_file_db @.dbname = 'pubs', @.physname =
'c:\MSSQL\Data\pubs.mdf'
If the above fail then try:- ( If you have a backup for the database then
use that file to restore)
1. Start database in emergency mode
Setting the database status to emergency mode tells SQL Server to skip
automatic recovery and lets you access the data.
To get your data, use this script:
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
You might be able to use bulk copy program (bcp), simple SELECT commands, or
use DTS to extract
your data while the database is in emergency mode. After this database will
be usable with out transaction log. AFter this
create a new database and use DTS to transfer objects and data
Thanks
Hari
MCDBA
"JK" <JK@.discussions.microsoft.com> wrote in message
news:1A30B41D-8840-4837-BE9C-588A3373C87B@.microsoft.com...[vbcol=seagreen]
> I tried this too then it gives error:
> An error occurred while processing the log for database 'name'
> Connection broken
> "Narayana Vyas Kondreddi" wrote:
>
try[vbcol=seagreen]
not[vbcol=seagreen]
and[vbcol=seagreen]
'D:\MSSQL\data\name_Log.LDF'[vbcol=seagreen]|||Why not do the following:
sp_configure 'allow updates',1
reconfigure with override
go
update sysdatabases
set status = 32768
where name = 'BADDBNAME'
go
dbcc rebuild_log('BADDBNAME','C:\NewLogFile.ldf')
go
update sysdatabases
set status = 0
where name = 'BADDBNAME'
go
sp_configure 'allow updates',0
reconfigure with override
Then it will create a new log file and it should be usable.
Just know that rebuild_log is not supported by Microsoft although I have
had it recommeneded to me by MSPSS in the past to solve corruption issues.
They make you sign a waiver before actually recommending it.
In article <udckLvtaEHA.3596@.tk2msftngp13.phx.gbl>,
hari_prasad_k@.hotmail.com said...
[vbcol=seagreen]
> Copy the original MDF and LDF to a safe location and try attaching the
> database only with MDF file. This will not work if you have more
> than 1 MDF and LDF files.
> EXEC sp_attach_single_file_db @.dbname = 'pubs', @.physname =
> 'c:\MSSQL\Data\pubs.mdf'
>
> If the above fail then try:- ( If you have a backup for the database then
> use that file to restore)
>
> 1. Start database in emergency mode
> Setting the database status to emergency mode tells SQL Server to skip
> automatic recovery and lets you access the data.
> To get your data, use this script:
> Sp_configure "allow updates", 1
> go
> Reconfigure with override
> GO
> Update sysdatabases set status = 32768 where name = "BadDbName"
> go
> Sp_configure "allow updates", 0
> go
> Reconfigure with override
> GO
> You might be able to use bulk copy program (bcp), simple SELECT commands,
or
> use DTS to extract
> your data while the database is in emergency mode. After this database wil
l
> be usable with out transaction log. AFter this
> create a new database and use DTS to transfer objects and data
> Thanks
> Hari
> MCDBA
> "JK" <JK@.discussions.microsoft.com> wrote in message
> news:1A30B41D-8840-4837-BE9C-588A3373C87B@.microsoft.com...
> try
> not
> and
> 'D:\MSSQL\data\name_Log.LDF'