Applying tranaction log to old data file

[koszuf]

Hi,
on my SQL Server data file is corrupted. Unfortunately last backup was
made about half year ago. Transaction log seems to be ok.
Is it possible to restore data file from old backup and apply
transactions from current transaction log?

regards

[koszuf]

As Erland said, you need to first backup the transaction log with
NO_TRUNCATE. *For example:

BACKUP LOG MyDatabase
TO DISK = 'C:\MyDatabase_Log.bak'
WITH NO_TRUNCATE;

Thanks for your response, but how can I make log backup, if my
transactiong log is not mounted. Should I restore old backup, manually
copy current transaction log and then create log backup or there is
other procedure?

[Dan Guzman]

Thanks for your response, but how can I make log backup, if my

transactiong log is not mounted. Should I restore old backup, manually
copy current transaction log and then create log backup or there is
other procedure?

You should be able to backup the transaction log using the TRUNCATE_ONLY
option even if the data file(s) is unavailable.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

<ko****@gmail.comwrote in message
news:78**********************************@d77g2000 hsb.googlegroups.com...

As Erland said, you need to first backup the transaction log with
NO_TRUNCATE. For example:

BACKUP LOG MyDatabase
TO DISK = 'C:\MyDatabase_Log.bak'
WITH NO_TRUNCATE;

Thanks for your response, but how can I make log backup, if my
transactiong log is not mounted. Should I restore old backup, manually
copy current transaction log and then create log backup or there is
other procedure?

[Sybaseguru]

>

You should be able to backup the transaction log using the TRUNCATE_ONLY
option even if the data file(s) is unavailable.

DO NOT DO TRUNCATE ONLY - YOU WILL LOOSE YOUR LOGS FOR GOOD!!!!!!!

The procedure you need is in 3 parts:-

Firstly you need to dump the 24gb transaction log - ie take it out of the
database onto a file system file. Read up on "dump tran...no_truncate".
This will work even if the data is stuffed as long as the log is intact.

Secondly you use you old dump from yonks ago to reset up the database. If
the disks are ok you can just use "load database"

Thirdly you apply the transaction log that you saved in the first step. read
up on "load tran"

The secret is in the planning of the recovery. If you've got space you can
create a whole new databse and do the recovery to that and when you are
happy rename the databases and cycle the server.

[Erland Sommarskog]

Dan Guzman (gu******@nospam-online.sbcglobal.net) writes:

>Thanks for your response, but how can I make log backup, if my
transactiong log is not mounted. Should I restore old backup, manually
copy current transaction log and then create log backup or there is
other procedure?

You should be able to backup the transaction log using the TRUNCATE_ONLY
option even if the data file(s) is unavailable.

You are thinking of NORECOVERY, aren't you? TRUNCATE_ONLY is not a very
good choice, as pointed out sybaseguru.

But it appears koszuf does not have his database attached, and in that
case, it's more difficult, since you cannot attach a broken MDF.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Dan Guzman]

DO NOT DO TRUNCATE ONLY - YOU WILL LOOSE YOUR LOGS FOR GOOD!!!!!!!

Yes, I meant NO_TRUNCATE as in the original example I posted. I don't
believe the TRUNCATE_ONLY option is allowed when the data files are
inaccessible.

Thanks for the correction.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Sybaseguru" <co****@usenet.cnntp.orgwrote in message
news:48***********************@read.cnntp.org...

>>
You should be able to backup the transaction log using the TRUNCATE_ONLY
option even if the data file(s) is unavailable.

DO NOT DO TRUNCATE ONLY - YOU WILL LOOSE YOUR LOGS FOR GOOD!!!!!!!

The procedure you need is in 3 parts:-

Firstly you need to dump the 24gb transaction log - ie take it out of the
database onto a file system file. Read up on "dump tran...no_truncate".
This will work even if the data is stuffed as long as the log is intact.

Secondly you use you old dump from yonks ago to reset up the database. If
the disks are ok you can just use "load database"

Thirdly you apply the transaction log that you saved in the first step.
read
up on "load tran"

The secret is in the planning of the recovery. If you've got space you can
create a whole new databse and do the recovery to that and when you are
happy rename the databases and cycle the server.

[Dan Guzman]

You are thinking of NORECOVERY, aren't you? TRUNCATE_ONLY is not a very

good choice, as pointed out sybaseguru.

Yes, I meant NO_TRUNCATE as in the original example I posted and NORECOVERY
during the full backup restore.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn*********************@127.0.0.1...

Dan Guzman (gu******@nospam-online.sbcglobal.net) writes:

>>Thanks for your response, but how can I make log backup, if my
transactiong log is not mounted. Should I restore old backup, manually
copy current transaction log and then create log backup or there is
other procedure?

You should be able to backup the transaction log using the TRUNCATE_ONLY
option even if the data file(s) is unavailable.

You are thinking of NORECOVERY, aren't you? TRUNCATE_ONLY is not a very
good choice, as pointed out sybaseguru.

But it appears koszuf does not have his database attached, and in that
case, it's more difficult, since you cannot attach a broken MDF.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx