|
Home > Archive > microsoft.public.sqlserver.server > December 2002 > Can we use .ldf file to restore a crashed database?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Can we use .ldf file to restore a crashed database?
|
|
| Hung Huynh 2002-12-19, 4:24 pm |
| My boss is using 4D database and he claims that he can use the log file to
restore a crash database. For instance, when a data file fails, all he needs
is a backup data file + the current live log file, then he can bring the
database upto the point right before the database fails.
I believe SQL Server 2000 doesn't function this way. To my knowledge, we can
only use the transaction logs backup to restore, not the live .ldf file to
do restore. So, if we do translog backup hourly, then we would lose 59
minutes worth of data in the worst case. Well, this is not good enough to
my boss. He doesn't believe SQL Server would do this. He doesn't want to
lose any piece of data in cases of database failures. That makes sense to
me, but I really don't know of a way to do this in SQL Server 2000.
I don't know of any other backup and restore methods to accomplish no data
loss in any circumstances. Would anyone suggest a way to accomplish this?
Thanks!
HH
| |
| Mike John 2002-12-19, 4:24 pm |
| This is relatively easy to achieve.
a. put Logs and data on separate discs.
b. Mirror the logs
If data discs go bang
1- backup log wth no_truncate
2- restore db from most recent backup
3 -restore logs up to point 1
4 restore log you backed up at 1
As always with this sort of thing ensure you fully understand BOL in this
area before committing to a recovery strategy.
Mike John
"Hung Huynh" <hungh@wi.rr.com> wrote in message
news:eqoOMd6pCHA.2752@TK2MSFTNGP09...
> My boss is using 4D database and he claims that he can use the log file to
> restore a crash database. For instance, when a data file fails, all he
needs
> is a backup data file + the current live log file, then he can bring the
> database upto the point right before the database fails.
>
> I believe SQL Server 2000 doesn't function this way. To my knowledge, we
can
> only use the transaction logs backup to restore, not the live .ldf file to
> do restore. So, if we do translog backup hourly, then we would lose 59
> minutes worth of data in the worst case. Well, this is not good enough to
> my boss. He doesn't believe SQL Server would do this. He doesn't want to
> lose any piece of data in cases of database failures. That makes sense to
> me, but I really don't know of a way to do this in SQL Server 2000.
>
> I don't know of any other backup and restore methods to accomplish no data
> loss in any circumstances. Would anyone suggest a way to accomplish this?
>
> Thanks!
>
> HH
>
>
| |
| Kalen Delaney 2002-12-19, 8:23 pm |
| You c
--
Kalen Delaney
SQL Server MVP
"Hung Huynh" <hungh@wi.rr.com> wrote in message
news:eqoOMd6pCHA.2752@TK2MSFTNGP09...
> My boss is using 4D database and he claims that he can use the log file to
> restore a crash database. For instance, when a data file fails, all he
needs
> is a backup data file + the current live log file, then he can bring the
> database upto the point right before the database fails.
>
> I believe SQL Server 2000 doesn't function this way. To my knowledge, we
can
> only use the transaction logs backup to restore, not the live .ldf file to
> do restore. So, if we do translog backup hourly, then we would lose 59
> minutes worth of data in the worst case. Well, this is not good enough to
> my boss. He doesn't believe SQL Server would do this. He doesn't want to
> lose any piece of data in cases of database failures. That makes sense to
> me, but I really don't know of a way to do this in SQL Server 2000.
>
> I don't know of any other backup and restore methods to accomplish no data
> loss in any circumstances. Would anyone suggest a way to accomplish this?
>
> Thanks!
>
> HH
>
>
| |
| Kalen Delaney 2002-12-19, 8:23 pm |
| Wow, sometimes by keyboard seems to have a mind of it's own and the cursor
jumps places I never intended.
I didn't mean to end that previous message so soon....
You can't use the 'live' log to restore, but you if a database crashes, and
both the master database and the drive on which the log resides are still
available, you can back up the tail of the log which includes all the
activity up to the point of the failure.
When you restore, you need to apply all the transaction logs, and the last
log to apply is the one that you made after the crash.
So, in order to use this technique, you have to make sure your log will be
safe, and that is why most people will install their logs on a mirrored
drive.
HTH
--
Kalen Delaney
SQL Server MVP
"Hung Huynh" <hungh@wi.rr.com> wrote in message
news:eqoOMd6pCHA.2752@TK2MSFTNGP09...
> My boss is using 4D database and he claims that he can use the log file to
> restore a crash database. For instance, when a data file fails, all he
needs
> is a backup data file + the current live log file, then he can bring the
> database upto the point right before the database fails.
>
> I believe SQL Server 2000 doesn't function this way. To my knowledge, we
can
> only use the transaction logs backup to restore, not the live .ldf file to
> do restore. So, if we do translog backup hourly, then we would lose 59
> minutes worth of data in the worst case. Well, this is not good enough to
> my boss. He doesn't believe SQL Server would do this. He doesn't want to
> lose any piece of data in cases of database failures. That makes sense to
> me, but I really don't know of a way to do this in SQL Server 2000.
>
> I don't know of any other backup and restore methods to accomplish no data
> loss in any circumstances. Would anyone suggest a way to accomplish this?
>
> Thanks!
>
> HH
>
>
|
|
|
|
|