DB Restore with Transaction File Only

hi,
I have a sql where data files and transaction files are
stored seperatedly... and one time a problem occured when
data files become corrupted and the only thing left is the
transaction file.... and the databases went offline... my
question is can i restore from a full db backup and then
attach the existing transaction files...?
No. It's not possible nor logical to just attach the trx log file to an
existing database. If you have made trx log backups consistently since the
last full backup, you might have a chance of restoring your database to its
previous state prior to the corruption.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"perry" <pramos@.codex-systems.com> wrote in message
news:7c8b01c4956b$985122f0$a601280a@.phx.gbl...
> hi,
> I have a sql where data files and transaction files are
> stored seperatedly... and one time a problem occured when
> data files become corrupted and the only thing left is the
> transaction file.... and the databases went offline... my
> question is can i restore from a full db backup and then
> attach the existing transaction files...?
|||perry,
Yes you can! As long as the transaction log chain (all transaction log
backups since your last full backup) is uninterrupted since your last
full backup it will work great. This scenario is exactly what the
transaction log backups are for. Good luck, if you need further help,
post back.
Look up transaction log in Books online, there's some great information
in there.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
perry wrote:
> hi,
> I have a sql where data files and transaction files are
> stored seperatedly... and one time a problem occured when
> data files become corrupted and the only thing left is the
> transaction file.... and the databases went offline... my
> question is can i restore from a full db backup and then
> attach the existing transaction files...?
|||perry,
After reading Peter's post I noticed you asked if you can *attach* the
transaction log file. No, you can't, you can however restore the
transaction log chain as mentioned in my post. Sorry for the error.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Mark Allison wrote:[vbcol=seagreen]
> perry,
> Yes you can! As long as the transaction log chain (all transaction log
> backups since your last full backup) is uninterrupted since your last
> full backup it will work great. This scenario is exactly what the
> transaction log backups are for. Good luck, if you need further help,
> post back.
> Look up transaction log in Books online, there's some great information
> in there.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> perry wrote:
|||But what if I have a singel transaction log backup which I
have set up to notrucate, waht is the possible data
recovery i can have......will all trasactions prior to
the last full database backup up to the trasaction backup
(which is run daily and overwrites it in a single file)
will be recovered...thanks for the post..your a great
help...

>--Original Message--
>No. It's not possible nor logical to just attach the trx
log file to an
>existing database. If you have made trx log backups
consistently since the
>last full backup, you might have a chance of restoring
your database to its
>previous state prior to the corruption.
>--
>Peter Yeoh
>http://www.yohz.com
>Need smaller SQL2K backup files? Use MiniSQLBackup Lite,
free![vbcol=seagreen]
>
>"perry" <pramos@.codex-systems.com> wrote in message
>news:7c8b01c4956b$985122f0$a601280a@.phx.gbl...
when[vbcol=seagreen]
the[vbcol=seagreen]
my
>
>.
>
|||Perry,
Are you saying that you do BACKUP LOG ... WITH NO_TRUNCATE?
If so, then the transaction log (the ldf file) will grow and grow and the space within never be
reused.
Anyhow, assuming that you do, you might be able to use such a backup. But if you read Books Online,
it only documents that option for doing a transaction log backup when the database is damaged...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"perry" <anonymous@.discussions.microsoft.com> wrote in message
news:7e8801c4957d$bc828ee0$a501280a@.phx.gbl...[vbcol=seagreen]
>
> But what if I have a singel transaction log backup which I
> have set up to notrucate, waht is the possible data
> recovery i can have......will all trasactions prior to
> the last full database backup up to the trasaction backup
> (which is run daily and overwrites it in a single file)
> will be recovered...thanks for the post..your a great
> help...
>
> log file to an
> consistently since the
> your database to its
> free!
> when
> the
> my
|||Like Tibor, I'm assuming you have consistently backed up the log with the
NO_TRUNCATE option (btw, strange thing to do). However, each of the trx log
backup will only contain the log data since the last log backup. It will
not store whatever log data is in the log file (I'm guessing you use the
NO_TRUNCATE option because you wanted your trx log backups to always contain
all log data since the last full backup). Did a quick test to confirm this.
I guess you now have a very large ldf file, and want to be able to recover
the data into the mdf file. No idea how this can be done, or even possible.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"perry" <anonymous@.discussions.microsoft.com> wrote in message
news:7e8801c4957d$bc828ee0$a501280a@.phx.gbl...[vbcol=seagreen]
>
> But what if I have a singel transaction log backup which I
> have set up to notrucate, waht is the possible data
> recovery i can have......will all trasactions prior to
> the last full database backup up to the trasaction backup
> (which is run daily and overwrites it in a single file)
> will be recovered...thanks for the post..your a great
> help...
>
> log file to an
> consistently since the
> your database to its
> free!
> when
> the
> my
|||Peter,
Interesting. Just so I understand you correctly, a scenario:
0. BACKUP LOG
1. BACKUP DB
2. BACKUP LOG NO_TRUNCATE
3. BACKUP LOG NO_TRUNCATE
4. BACKUP LOG NO_TRUNCATE
Status now is:
The ldf file contains all log records since 0.
The backup file produced in step 4 contains the log records since 3.
Is above what you test showed? If so, then my gut feeling about NO_TRUNCATE is correct and the name
of the parameter is misleading (something I've been trying to communicate to MS with little
success). The name of the option implies that you can keep doing log backups and only apply the
latest. I've always explained this option from what it is documented as in Books Online, to backup
log of a broken database...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter Yeoh" <nospam@.nospam.com> wrote in message news:ePpBp9YlEHA.592@.TK2MSFTNGP11.phx.gbl...
> Like Tibor, I'm assuming you have consistently backed up the log with the
> NO_TRUNCATE option (btw, strange thing to do). However, each of the trx log
> backup will only contain the log data since the last log backup. It will
> not store whatever log data is in the log file (I'm guessing you use the
> NO_TRUNCATE option because you wanted your trx log backups to always contain
> all log data since the last full backup). Did a quick test to confirm this.
> I guess you now have a very large ldf file, and want to be able to recover
> the data into the mdf file. No idea how this can be done, or even possible.
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "perry" <anonymous@.discussions.microsoft.com> wrote in message
> news:7e8801c4957d$bc828ee0$a501280a@.phx.gbl...
>
|||> Status now is:
> The ldf file contains all log records since 0.
> The backup file produced in step 4 contains the log records since 3.
Right on. The backup file size in step 4 is not the cumulative size of the
files produced in step 2 and 3.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23GGEXMZlEHA.2948@.TK2MSFTNGP11.phx.gbl...
> Peter,
> Interesting. Just so I understand you correctly, a scenario:
> 0. BACKUP LOG
> 1. BACKUP DB
> 2. BACKUP LOG NO_TRUNCATE
> 3. BACKUP LOG NO_TRUNCATE
> 4. BACKUP LOG NO_TRUNCATE
> Status now is:
> The ldf file contains all log records since 0.
> The backup file produced in step 4 contains the log records since 3.
> Is above what you test showed? If so, then my gut feeling about
NO_TRUNCATE is correct and the name
> of the parameter is misleading (something I've been trying to communicate
to MS with little
> success). The name of the option implies that you can keep doing log
backups and only apply the
> latest. I've always explained this option from what it is documented as in
Books Online, to backup
> log of a broken database...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Peter Yeoh" <nospam@.nospam.com> wrote in message
news:ePpBp9YlEHA.592@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
the[vbcol=seagreen]
log[vbcol=seagreen]
will[vbcol=seagreen]
contain[vbcol=seagreen]
this.[vbcol=seagreen]
recover[vbcol=seagreen]
possible.
>
|||> Right on. The backup file size in step 4 is not the cumulative size of the
> files produced in step 2 and 3.
Indeed! I just wrote an article on the topic, with some SQL code proving the point...
http://www.karaszi.com/SQLServer/inf...o_truncate.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter Yeoh" <nospam@.nospam.com> wrote in message news:e$ni%23PZlEHA.2224@.tk2msftngp13.phx.gbl...
> Right on. The backup file size in step 4 is not the cumulative size of the
> files produced in step 2 and 3.
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23GGEXMZlEHA.2948@.TK2MSFTNGP11.phx.gbl...
> NO_TRUNCATE is correct and the name
> to MS with little
> backups and only apply the
> Books Online, to backup
> news:ePpBp9YlEHA.592@.TK2MSFTNGP11.phx.gbl...
> the
> log
> will
> contain
> this.
> recover
> possible.
>