Restore SQL database

Hi,
There are two recovery models of SQL Server databases. The "SIMPLE" and
"FULL" and also bulked but I am not interested in that.
I need a full recovery model as I need to recover the most amount of data
and the last full backup from the night before is insufficient.
Question: In order to restore an SQL database to its last transaction, do I
restore the most recent database backup and use the existing LDF file to
bring SQL to its last transaction'
OR
Do I have to restore the last SQL database backup and then restore the last
transactional backup?
I guess if the Server disappears for whatever reason, the existing LDF would
be gone to and you would only be left with the previous nights database
backup. From this perspective I think you need both the last database and
transactional backups.
But what if database corruption occurs...can I restore the previous nights
database backups and use the existing ldf file of the corrupted database'
I am trying to figure out the best way to try and recover all SQL data in
the event of any disaster rather than just using the previous nights backup?
Your help and patience is greatly appreciated.
Thanking You
ElvisHi,
This was answered by Steen:
"How often you backup your log, depends on how many hours of work you (or
your company) can accept to loose in case of a break down.
Let me try to bring up a few examples.
1. You run a FULL backup at 2 AM and no log backup. First of all this
will not truncate your logfile hence it will continue to grow. If
something goes wrong with the database e.g. at 1.55 AM and you need to
restore your backup, you'll loose 23 hours and 55 minutes of work.
2. You run a FULL backup at 2 AM and a log backup at 2.30 AM. This will
truncate your logfile so it doesn't grow, but that's a bout all good the
log backup will do for you. If something happens at 1.55 AM, you'll
loose 23 hours and 25 minutes of work. You can restore your full backup
from 2 AM and then apply the log from 2.30 AM and then your database
will contain the data it had at 2.30 AM when you did the log backup.
3. You run a FULL backup at 2 AM and a log backup at 2 PM. This will
still truncate your logfile, but from a recovery point of view you're
doing a little better. Again if something happens at 1.55 AM you'll only
loose 11 hours and 55 minutes of work.
4. You run a FULL backup at 2 AM and do log backups every hour from 8 AM
to 6 PM (assuming that's your average working day). Now if something
happens at e.g. 2.30 PM, you'll only loose 30 minutes of work. You
simply restore your FULL backup from 2 AM and then all the hourly log
backup up to 2 PM.
If you do FULL backup and log backups, you are "always" guaranteed that
you can restore your database up to the state it had at the time of
latest log file backup. In some case though - depending on what happens
to your database, you'll be able to backup your logfile after the
problem ocoured and then you can restore the logfile with the STOPAT
command so you can restore it up to the time just before things went
wrong meaning your data loss will be minimal.
I hope the above cleared things up a little bit. Just keep in mind that
there are no definitive answer to how often you should backup your log -
it all depends on how much data you are willing to loose in case of a
failure of some kind. In many cases there aren't really any penalty for
backing up too much - but there is for not having the right backup at
the right time...:-).
--
Regards
Steen Schlüter Persson
DBA"
"Elvis" wrote:

> Hi,
> There are two recovery models of SQL Server databases. The "SIMPLE" and
> "FULL" and also bulked but I am not interested in that.
> I need a full recovery model as I need to recover the most amount of data
> and the last full backup from the night before is insufficient.
> Question: In order to restore an SQL database to its last transaction, do
I
> restore the most recent database backup and use the existing LDF file to
> bring SQL to its last transaction'
> OR
> Do I have to restore the last SQL database backup and then restore the las
t
> transactional backup?
> I guess if the Server disappears for whatever reason, the existing LDF wou
ld
> be gone to and you would only be left with the previous nights database
> backup. From this perspective I think you need both the last database and
> transactional backups.
> But what if database corruption occurs...can I restore the previous night
s
> database backups and use the existing ldf file of the corrupted database'
> I am trying to figure out the best way to try and recover all SQL data in
> the event of any disaster rather than just using the previous nights backu
p?
> Your help and patience is greatly appreciated.
> Thanking You
> Elvis|||Note, depending on the form of the failure you may be able to back up the
tail of the log and lose virtually no work.
I highly recommend buying a book on the subject if your needs warrant.
And practice. Nothing is worse than having a real disaster and trying to
recover/restore a DB for the first time with the CEO breathing down your
neck.
"Elvis" <Elvis@.discussions.microsoft.com> wrote in message
news:024601F3-A046-41C3-971E-D2F985D6D394@.microsoft.com...[vbcol=seagreen]
> Hi,
> This was answered by Steen:
> "How often you backup your log, depends on how many hours of work you (or
> your company) can accept to loose in case of a break down.
> Let me try to bring up a few examples.
> 1. You run a FULL backup at 2 AM and no log backup. First of all this
> will not truncate your logfile hence it will continue to grow. If
> something goes wrong with the database e.g. at 1.55 AM and you need to
> restore your backup, you'll loose 23 hours and 55 minutes of work.
> 2. You run a FULL backup at 2 AM and a log backup at 2.30 AM. This will
> truncate your logfile so it doesn't grow, but that's a bout all good the
> log backup will do for you. If something happens at 1.55 AM, you'll
> loose 23 hours and 25 minutes of work. You can restore your full backup
> from 2 AM and then apply the log from 2.30 AM and then your database
> will contain the data it had at 2.30 AM when you did the log backup.
> 3. You run a FULL backup at 2 AM and a log backup at 2 PM. This will
> still truncate your logfile, but from a recovery point of view you're
> doing a little better. Again if something happens at 1.55 AM you'll only
> loose 11 hours and 55 minutes of work.
> 4. You run a FULL backup at 2 AM and do log backups every hour from 8 AM
> to 6 PM (assuming that's your average working day). Now if something
> happens at e.g. 2.30 PM, you'll only loose 30 minutes of work. You
> simply restore your FULL backup from 2 AM and then all the hourly log
> backup up to 2 PM.
> If you do FULL backup and log backups, you are "always" guaranteed that
> you can restore your database up to the state it had at the time of
> latest log file backup. In some case though - depending on what happens
> to your database, you'll be able to backup your logfile after the
> problem ocoured and then you can restore the logfile with the STOPAT
> command so you can restore it up to the time just before things went
> wrong meaning your data loss will be minimal.
> I hope the above cleared things up a little bit. Just keep in mind that
> there are no definitive answer to how often you should backup your log -
> it all depends on how much data you are willing to loose in case of a
> failure of some kind. In many cases there aren't really any penalty for
> backing up too much - but there is for not having the right backup at
> the right time...:-).
> --
> Regards
> Steen Schlter Persson
> DBA"
>
> "Elvis" wrote:
>
data[vbcol=seagreen]
do I[vbcol=seagreen]
last[vbcol=seagreen]
would[vbcol=seagreen]
and[vbcol=seagreen]
nights[vbcol=seagreen]
database'[vbcol=seagreen]
in[vbcol=seagreen]
backup?[vbcol=seagreen]sql