Applying tranaction log to old data file

[koszuf@gmail.com]

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@gmail.com]

Re: Applying tranaction log to old data file

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]

Re: Applying tranaction log to old data file

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/

<koszuf@gmail.c omwrote in message
news:7858f9ba-fd20-44d6-b149-82dd9427b0b0@d7 7g2000hsb.googl egroups.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]

Re: Applying tranaction log to old data file

>

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_trunc ate".
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]

Re: Applying tranaction log to old data file

Dan Guzman (guzmanda@nospa m-online.sbcgloba l.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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Dan Guzman]

Re: Applying tranaction log to old data file

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 " <collap@usenet. cnntp.orgwrote in message
news:4863d830$0 $25950$6e1ede2f @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_trunc ate".
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]

Re: Applying tranaction log to old data file

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" <esquel@sommars kog.sewrote in message
news:Xns9ACA17C F59B5Yazorman@1 27.0.0.1...

Dan Guzman (guzmanda@nospa m-online.sbcgloba l.net) writes:

>>Thanks for your response, but how can I make log backup, if my
>>transaction g 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, esquel@sommarsk og.se
>
Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

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