Restoring Transaction Logs Into Backups of MDF and LDF

Hello,
Here's a brief outline of my scenario:
I am trying to recover from a database corruption problem. I have a
backup of the db's physical MDF and LDF files from before the
corruption, but no actual backup (ie bak file). I also have daily
transaction log backups right up to today, and my live db is still
operation despite the corruption, so I can take another transaction
log backup when I do the repair.
The problem I have is I don't know how to, or if it's possible to
attach the backup MDF & LDF files and then somehow apply my
transaction log backups to build a new up to date database.
Thanks,
AlexYou would need Attach with NORECOVERY, which currently is not a possibility.
There is a connect item you can vote in for a future feature request.
https://connect.microsoft.com/SQLSe...=2706
51
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<cosmasdev@.yahoo.com> wrote in message
news:1177078845.141070.271000@.n59g2000hsh.googlegroups.com...
> Hello,
> Here's a brief outline of my scenario:
> I am trying to recover from a database corruption problem. I have a
> backup of the db's physical MDF and LDF files from before the
> corruption, but no actual backup (ie bak file). I also have daily
> transaction log backups right up to today, and my live db is still
> operation despite the corruption, so I can take another transaction
> log backup when I do the repair.
> The problem I have is I don't know how to, or if it's possible to
> attach the backup MDF & LDF files and then somehow apply my
> transaction log backups to build a new up to date database.
> Thanks,
> Alex
>|||Alex,
If you don't mind my asking, what kind of corruption did you get and how did
it happen. We're just moving to SQLServer to avoid corruptions and I'd hate
to find that we did not leave the problem behind.
Regards
Brian Morris
<cosmasdev@.yahoo.com> wrote in message
news:1177078845.141070.271000@.n59g2000hsh.googlegroups.com...
> Hello,
> Here's a brief outline of my scenario:
> I am trying to recover from a database corruption problem. I have a
> backup of the db's physical MDF and LDF files from before the
> corruption, but no actual backup (ie bak file). I also have daily
> transaction log backups right up to today, and my live db is still
> operation despite the corruption, so I can take another transaction
> log backup when I do the repair.
> The problem I have is I don't know how to, or if it's possible to
> attach the backup MDF & LDF files and then somehow apply my
> transaction log backups to build a new up to date database.
> Thanks,
> Alex
>

Restoring Transaction Logs Into Backups of MDF and LDF

Hello,
Here's a brief outline of my scenario:
I am trying to recover from a database corruption problem. I have a
backup of the db's physical MDF and LDF files from before the
corruption, but no actual backup (ie bak file). I also have daily
transaction log backups right up to today, and my live db is still
operation despite the corruption, so I can take another transaction
log backup when I do the repair.
The problem I have is I don't know how to, or if it's possible to
attach the backup MDF & LDF files and then somehow apply my
transaction log backups to build a new up to date database.
Thanks,
AlexYou would need Attach with NORECOVERY, which currently is not a possibility.
There is a connect item you can vote in for a future feature request.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=270651
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<cosmasdev@.yahoo.com> wrote in message
news:1177078845.141070.271000@.n59g2000hsh.googlegroups.com...
> Hello,
> Here's a brief outline of my scenario:
> I am trying to recover from a database corruption problem. I have a
> backup of the db's physical MDF and LDF files from before the
> corruption, but no actual backup (ie bak file). I also have daily
> transaction log backups right up to today, and my live db is still
> operation despite the corruption, so I can take another transaction
> log backup when I do the repair.
> The problem I have is I don't know how to, or if it's possible to
> attach the backup MDF & LDF files and then somehow apply my
> transaction log backups to build a new up to date database.
> Thanks,
> Alex
>|||Alex,
If you don't mind my asking, what kind of corruption did you get and how did
it happen. We're just moving to SQLServer to avoid corruptions and I'd hate
to find that we did not leave the problem behind.
Regards
Brian Morris
<cosmasdev@.yahoo.com> wrote in message
news:1177078845.141070.271000@.n59g2000hsh.googlegroups.com...
> Hello,
> Here's a brief outline of my scenario:
> I am trying to recover from a database corruption problem. I have a
> backup of the db's physical MDF and LDF files from before the
> corruption, but no actual backup (ie bak file). I also have daily
> transaction log backups right up to today, and my live db is still
> operation despite the corruption, so I can take another transaction
> log backup when I do the repair.
> The problem I have is I don't know how to, or if it's possible to
> attach the backup MDF & LDF files and then somehow apply my
> transaction log backups to build a new up to date database.
> Thanks,
> Alex
>

Restoring SQL Server Data

hi. I just ran a dts that overwrote my data in my sql server 2000 database. Is it possible to still recover my old data?if you are running in full backup mode and you just ran DML statements, backup the transaction log of the database and restore the whole backup (full + evtl. differential + transaction log) with the option point -in-time recovery, stopping the restore until a specific time. if you don′t have the full backup model or no full backup, then there is no chance.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

I agree with the above answer, but if you have not been taking backups you will not be able to recover the database.

sql

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'

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
JK
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...
> 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-SQL 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 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
>
>
|||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 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...
> try
> not
> and
> 'D:\MSSQL\data\name_Log.LDF'