Power Failure for SQL Server 2000

We encounter a power failure for about half an hour and
the SQL Server 2000 is back again.
For production databases, we use FULL Recovery Model with
Transaction Log backed up every half an hour. After power
is up, everything works properly.
I would like to know what happens to the SQL Server 2000
when the power fails and how the data is recovered when
power is up again.
Thanks
Regardless of the recovery model, each database is automatically recovered
when the instance starts. Data are read from the transaction log since the
last checkpoint and applied to the database. Uncommitted transactions are
then rolled back. The end result is that the database is recovered to the
point of the failure, less uncommitted transactions.
Your FULL recovery model and log backups provide extra protection in the
event of media loss due to hardware failure or data corruption.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:01f401c5446e$a4183b70$a601280a@.phx.gbl...
> We encounter a power failure for about half an hour and
> the SQL Server 2000 is back again.
> For production databases, we use FULL Recovery Model with
> Transaction Log backed up every half an hour. After power
> is up, everything works properly.
> I would like to know what happens to the SQL Server 2000
> when the power fails and how the data is recovered when
> power is up again.
> Thanks
|||Dear Dan,
Thank you for your advice.
However, for SIMPLE Recovery Model, there will be no
transaction log backup. To what state does the database
recovered to ?
Besides, would you mind to elaborate on the extra benefit
of using FULL Recovery Model ?
Thanks again.

>--Original Message--
>Regardless of the recovery model, each database is
automatically recovered
>when the instance starts. Data are read from the
transaction log since the
>last checkpoint and applied to the database. Uncommitted
transactions are
>then rolled back. The end result is that the database is
recovered to the
>point of the failure, less uncommitted transactions.
>Your FULL recovery model and log backups provide extra
protection in the
>event of media loss due to hardware failure or data
corruption.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:01f401c5446e$a4183b70$a601280a@.phx.gbl...
with[vbcol=seagreen]
power
>
>.
>
|||Peter
<http://vyaskn.tripod.com/sql_server_...ices.htm#Step1
> --administaiting best practices
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:077101c54475$9661ee70$a501280a@.phx.gbl...[vbcol=seagreen]
> Dear Dan,
> Thank you for your advice.
> However, for SIMPLE Recovery Model, there will be no
> transaction log backup. To what state does the database
> recovered to ?
> Besides, would you mind to elaborate on the extra benefit
> of using FULL Recovery Model ?
> Thanks again.
> automatically recovered
> transaction log since the
> transactions are
> recovered to the
> protection in the
> corruption.
> message
> with
> power
|||Automatic recovery (what happens when you start SQL Server) doesn't have anything to do with
backups. SQL Server records all modifications in the transaction log, regardless of recovery model.
In simple, SQL Server removes log records from the transaction log when they aren't needed anymore
for this automatic recovery.
Full recovery model allow you to backup transaction log. This has a lot of advantages, like backup
log even of the database becomes corrupt, point in time restore etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:077101c54475$9661ee70$a501280a@.phx.gbl...[vbcol=seagreen]
> Dear Dan,
> Thank you for your advice.
> However, for SIMPLE Recovery Model, there will be no
> transaction log backup. To what state does the database
> recovered to ?
> Besides, would you mind to elaborate on the extra benefit
> of using FULL Recovery Model ?
> Thanks again.
> automatically recovered
> transaction log since the
> transactions are
> recovered to the
> protection in the
> corruption.
> message
> with
> power
|||To add to the other responses, automatic recovery will recover databases to
the same consistent state regardless of the recovery model.
Separately, database and transaction log backups reduce your vulnerability
to potential data loss. For example, if your power outage caused a hardware
problem that corrupted your log file, you could still restore from your most
recent database backup and then apply your log backups. At most, you would
lose one half hour of work. If only data files were lost, you could backup
the current log with NO_TRUNCATE and then restore your database and log
backups. No data would be lost in this case.
In the SIMPLE recovery model, your only recourse after losing data or log
files is to restore from your most recent database backup. All data
modifications since the backup would be lost.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:077101c54475$9661ee70$a501280a@.phx.gbl...[vbcol=seagreen]
> Dear Dan,
> Thank you for your advice.
> However, for SIMPLE Recovery Model, there will be no
> transaction log backup. To what state does the database
> recovered to ?
> Besides, would you mind to elaborate on the extra benefit
> of using FULL Recovery Model ?
> Thanks again.
> automatically recovered
> transaction log since the
> transactions are
> recovered to the
> protection in the
> corruption.
> message
> with
> power