Hourly Log Backups... restore proceedure

Hello all,
I am very to sql and still learning so please bear with what may be simple
questions.
I have a SqlDataBase that is at around 2.5GB's my log file was growing to
3GB each day. My maintenance job#1 happens overnight and does everything -
optimize, backup data and log files to disk etc. This backup goes to tape at
4:00AM with the complete server backup . I have another complete database
and log backup that goes direct to a different tape at 4:00PM.
This morning purged the log and shrunk the database to start from a clean
slate Database at 2.5GB's log started at close to nothing. I have setup an
hourly backup of the LOG FILE ONLY to a separate disk keeping 4 hours worth
of logs. I am now looking at the backups and they are 6264KB, 3952KB,
7036KB, 3313KB over the last four hours. We are not busy today but this does
not seem possible does this make sense?
My Question is this: given the proceedure above if the server blew up how do
I get a complete restore of the database... should my hourly backups include
the database as well as the transaction log?
MMaureen,
If you have not already done so, you should read:
Designing a Backup and Restore Strategy
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad
_bkprst_63eh.asp
In brief,
1. 3 to 7 meg of log file in a couple of hours is not very much.
2. Backup of the transactions is how you keep the log from growing. (Except
for simple recovery model)
3. If you have a backup of the database and the hour transaction logs you
would restore the same way:
Restore Database, Restore Log 1, Restore Log 2, ..., Restore Log N.
You should definitely do a trial disaster recovery to learn whether your
whole process is as realiable as you need.
Russell Fields
"Maureen" <nosend2me@.hotmail.com> wrote in message
news:ehBJYtNwDHA.1600@.TK2MSFTNGP10.phx.gbl...
> Hello all,
> I am very to sql and still learning so please bear with what may be simple
> questions.
> I have a SqlDataBase that is at around 2.5GB's my log file was growing to
> 3GB each day. My maintenance job#1 happens overnight and does everything -
> optimize, backup data and log files to disk etc. This backup goes to tape
at
> 4:00AM with the complete server backup . I have another complete database
> and log backup that goes direct to a different tape at 4:00PM.
> This morning purged the log and shrunk the database to start from a clean
> slate Database at 2.5GB's log started at close to nothing. I have setup an
> hourly backup of the LOG FILE ONLY to a separate disk keeping 4 hours
worth
> of logs. I am now looking at the backups and they are 6264KB, 3952KB,
> 7036KB, 3313KB over the last four hours. We are not busy today but this
does
> not seem possible does this make sense?
> My Question is this: given the proceedure above if the server blew up how
do
> I get a complete restore of the database... should my hourly backups
include
> the database as well as the transaction log?
> M
>|||Russell,
Thank for your post. If I understand you correctly To restore I need the
last DB Backup PLUS all LOG backups. In my case if the server failed at 1:49
PM I would need:
4:00 AM Complete database backup
PLUS [8:00AM Logbackup]
PLUS [9:00AM Logbackup]
PLUS [10:00AM Logbackup]
PLUS [11:00AM Logbackup]
PLUS [12:00PM Logbackup]
PLUS [1:00PM Logbackup]
And I would be good as of 1:00PM and I loose 49 minutes of the day. If I
have only the last four hours of logs... I am in trouble, is this correct?
My options are to either keep all of the hourly logs or do additional
database backups. I was actually surprised that the log backups are soooo
small given that the may daily log growth is 3GB I now seem to understand
that log backups must be considered as "A SET" since the last database
backup.
Am I close to understanding this correctly?
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:eNqVj3NwDHA.1060@.TK2MSFTNGP12.phx.gbl...
> Maureen,
> If you have not already done so, you should read:
> Designing a Backup and Restore Strategy
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad
> _bkprst_63eh.asp
> In brief,
> 1. 3 to 7 meg of log file in a couple of hours is not very much.
> 2. Backup of the transactions is how you keep the log from growing.
(Except
> for simple recovery model)
> 3. If you have a backup of the database and the hour transaction logs you
> would restore the same way:
> Restore Database, Restore Log 1, Restore Log 2, ..., Restore Log N.
> You should definitely do a trial disaster recovery to learn whether your
> whole process is as realiable as you need.
> Russell Fields
> "Maureen" <nosend2me@.hotmail.com> wrote in message
> news:ehBJYtNwDHA.1600@.TK2MSFTNGP10.phx.gbl...
> > Hello all,
> >
> > I am very to sql and still learning so please bear with what may be
simple
> > questions.
> >
> > I have a SqlDataBase that is at around 2.5GB's my log file was growing
to
> > 3GB each day. My maintenance job#1 happens overnight and does
everything -
> > optimize, backup data and log files to disk etc. This backup goes to
tape
> at
> > 4:00AM with the complete server backup . I have another complete
database
> > and log backup that goes direct to a different tape at 4:00PM.
> >
> > This morning purged the log and shrunk the database to start from a
clean
> > slate Database at 2.5GB's log started at close to nothing. I have setup
an
> > hourly backup of the LOG FILE ONLY to a separate disk keeping 4 hours
> worth
> > of logs. I am now looking at the backups and they are 6264KB, 3952KB,
> > 7036KB, 3313KB over the last four hours. We are not busy today but this
> does
> > not seem possible does this make sense?
> >
> > My Question is this: given the proceedure above if the server blew up
how
> do
> > I get a complete restore of the database... should my hourly backups
> include
> > the database as well as the transaction log?
> >
> > M
> >
> >
>|||Maureen,
Some comments in-line below.
Russell
> Thank for your post. If I understand you correctly To restore I need the
> last DB Backup PLUS all LOG backups. In my case if the server failed at
1:49
> PM I would need:
> 4:00 AM Complete database backup
> PLUS [8:00AM Logbackup]
> PLUS [9:00AM Logbackup]
> PLUS [10:00AM Logbackup]
> PLUS [11:00AM Logbackup]
> PLUS [12:00PM Logbackup]
> PLUS [1:00PM Logbackup]
> And I would be good as of 1:00PM and I loose 49 minutes of the day.
Correct. You would be able to restore each of the logs serially and get
back to 1:00 PM. (So don't throw away your logs.) With regard to the last
49 minutes, BACKUP... NO_TRUNCATE is for backing up the log of a damaged
database, so it is sometimes possible to recover those last minutes as well.
There are more details in the Books Online.
> If I have only the last four hours of logs... I am in trouble, is this
correct?
> My options are to either keep all of the hourly logs or do additional
> database backups. I was actually surprised that the log backups are soooo
> small given that the may daily log growth is 3GB I now seem to understand
> that log backups must be considered as "A SET" since the last database
> backup.
Yes, every log since the last database backup is a "set" that is needed to
restore the database to current. (Read the whole section on Backing Up and
Restoring Databases in the BOL.)
Also, I suspect that the major growth in your log may be caused by your
nighttime maintenance tasks.
> Am I close to understanding this correctly?
Yes. Keep learning and it will all make good sense.
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:eNqVj3NwDHA.1060@.TK2MSFTNGP12.phx.gbl...
> > Maureen,
> >
> > If you have not already done so, you should read:
> > Designing a Backup and Restore Strategy
> >
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad
> > _bkprst_63eh.asp
> >
> > In brief,
> > 1. 3 to 7 meg of log file in a couple of hours is not very much.
> > 2. Backup of the transactions is how you keep the log from growing.
> (Except
> > for simple recovery model)
> > 3. If you have a backup of the database and the hour transaction logs
you
> > would restore the same way:
> > Restore Database, Restore Log 1, Restore Log 2, ..., Restore Log N.
> >
> > You should definitely do a trial disaster recovery to learn whether your
> > whole process is as realiable as you need.
> >
> > Russell Fields
> >
> > "Maureen" <nosend2me@.hotmail.com> wrote in message
> > news:ehBJYtNwDHA.1600@.TK2MSFTNGP10.phx.gbl...
> > > Hello all,
> > >
> > > I am very to sql and still learning so please bear with what may be
> simple
> > > questions.
> > >
> > > I have a SqlDataBase that is at around 2.5GB's my log file was growing
> to
> > > 3GB each day. My maintenance job#1 happens overnight and does
> everything -
> > > optimize, backup data and log files to disk etc. This backup goes to
> tape
> > at
> > > 4:00AM with the complete server backup . I have another complete
> database
> > > and log backup that goes direct to a different tape at 4:00PM.
> > >
> > > This morning purged the log and shrunk the database to start from a
> clean
> > > slate Database at 2.5GB's log started at close to nothing. I have
setup
> an
> > > hourly backup of the LOG FILE ONLY to a separate disk keeping 4 hours
> > worth
> > > of logs. I am now looking at the backups and they are 6264KB, 3952KB,
> > > 7036KB, 3313KB over the last four hours. We are not busy today but
this
> > does
> > > not seem possible does this make sense?
> > >
> > > My Question is this: given the proceedure above if the server blew up
> how
> > do
> > > I get a complete restore of the database... should my hourly backups
> > include
> > > the database as well as the transaction log?
> > >
> > > M
> > >
> > >
> >
> >
>